Page Numbers in Excel: records

Showing posts with label records. Show all posts

Friday, March 30, 2012

Parameter Problem

Hello:
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>

Tuesday, March 20, 2012

Pair of records

Hi folks,

I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B

I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

Any urgent help will be highly appreicated.

Thanksany suggestion on my problem?|||possibly the reason no one answered within such a short time (it is courtesy not to bump your own question at all, but if you feel you must, give it more than just a few hours) is that your question is not very clear

here's one solution:create table pairs
( sr_no smallint not null
, week_no varchar(9)
)

insert into pairs values (1, '24-A')
insert into pairs values (2, '24-B')
insert into pairs values (3, '24-C')
insert into pairs values (4, '25-A')
insert into pairs values (5, '25-B')
insert into pairs values (6, '26-A')
insert into pairs values (7, '26-B')

select one.week_no
, two.week_no
from pairs one
, pairs two
where one.sr_no
= two.sr_no - 1
and floor(one.sr_no/2)
< floor((one.sr_no+1)/2)

24-A 24-B
24-C 25-A
25-B 26-A|||I am controlling data with Week numbers instead. So i am trying to get data of 24 and 25 togather and so one. Sr. Number is nothing but increament field.

Thanks for reply Mr. r937|||your data is very confusing

how about showing some rows of real data, and then show what results you expect

and which is it, oracle or sql server?|||here is your solution:select week_no/2
, amount
from yourtable
group
by week_no/2|||week_no/2?

You lost me on that one Rudy!

Mr roomi, I think your problem is that your table does not follow basic rules of normalization. You are trying to store two values ("24", and "A", for instance) in a single field. This makes it difficult to sort or group by the column's components.

Ideally, break the column into two different columns. If that is not an option, then you can simulate a "week_number" column using a formula:
Select left(week_no, charindex('-', week_no)-1) as week_number

(Double-check the syntax. I've been in Oracle recently and my TSQL is a little rusty...)|||week_no/2?

You lost me on that one Rudy!piece o' cake, my good man

divide the week number by 2, and because it's an integer, the result is an integer

so 22/2 is 11, 23/2 is 11, 24/2 is 12, 25/2 is 12, and so on

this allows you to group the pairs of weeks -- 22 with 23, 24 with 25, and so on

as for the normalization, check the other thread, it has better sample data, there's nothing wrong with the design|||aw crap, i meant to cross link the guy's other post and forgot to

http://www.dbforums.com/t1022906.html

guy has two identical threads going on in separate forums|||Jeez, a cross-post. This is a forum, not a freaking scavenger hunt.

I was baffled how you were going to divide the string "24-A" by two...

Mr. Roomi, here is a rule of thumb:
"You cannot code a problem clearly and simply until you can state the problem clearly and simply."

Really. I deal with people all too frequently who start coding without being able to define the problem, and they never get very far.

Paging, Soring, Filtering Already-written SPs Result

Hi guys,
we developed a large-scale web-application which uses sqlserver 2005,
and we created all of SPs for geting list of particular records or
geting a record info.
i.e Products_GetList | Products_GetInfo & etc.
now we need to implement paging,sorting or filtering on these SPs
results...
& we couldn't do this at webapplication level because of huge number of
records... also it's not possible to change all of these SPs(we have
more than 300 SPs already)
in other words i'm searching for a way to create a generic method(SP or
UDF) which do the paging & other operations on result of all SPs if
required.
i.e :
MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
[Generic SP (do sorting)] --> Specific SP
ThanksHi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Khafancoder" <khafancoder@.gmail.com> wrote in message
news:1168506782.577051.145700@.k58g2000hse.googlegroups.com...
> Hi guys,
> we developed a large-scale web-application which uses sqlserver 2005,
> and we created all of SPs for geting list of particular records or
> geting a record info.
> i.e Products_GetList | Products_GetInfo & etc.
> now we need to implement paging,sorting or filtering on these SPs
> results...
> & we couldn't do this at webapplication level because of huge number of
> records... also it's not possible to change all of these SPs(we have
> more than 300 SPs already)
> in other words i'm searching for a way to create a generic method(SP or
> UDF) which do the paging & other operations on result of all SPs if
> required.
> i.e :
> MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
> [Generic SP (do sorting)] --> Specific SP
>
> Thanks
>|||IMO the solution is to redesign, rather than attempt a workaround. You might
think you could use wrapper procedure calls for each stored proc which
caches the results in a temp table and sorts them but the syntax for this is
"insert into #yourtable exec yourproc", so the temp table definition must
already exist and each wrapper proc will therefore be different. Whichever
way you look at it, this will require widespread changes. The sorted column
will need to be provided, and as you are using paging, the page size and
page number will be provided. So, new parameters will get added to each
stored proc call from the application and each stored proc needs editing.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks,
yes, as u said this wrapper method (i.e for sorting) should get
SortColumnName, Asc|Desc and SP name...
if we ignore the temp-table structure problem, other problems will
solved, for example if all of these SPs return a resultset in a
specific format then the wrapper method could insert them in a generic
temp-table and sort them and finally return them.
in this approach i can call "insert into #generictemptable exec SPName"
dynamically by using execute cmd, but i think it will affect on
application performance...
how about CLR Integration ? could we use it to write the wrapper ?
Paul Ibison wrote:
> IMO the solution is to redesign, rather than attempt a workaround. You might
> think you could use wrapper procedure calls for each stored proc which
> caches the results in a temp table and sorts them but the syntax for this is
> "insert into #yourtable exec yourproc", so the temp table definition must
> already exist and each wrapper proc will therefore be different. Whichever
> way you look at it, this will require widespread changes. The sorted column
> will need to be provided, and as you are using paging, the page size and
> page number will be provided. So, new parameters will get added to each
> stored proc call from the application and each stored proc needs editing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||My understanding of CLR integration is that it is great for iterative tasks
and pattern matching but this is pretty standard SQL so I don't see any
benefit there. In the code-behind you could use some paging code applied to
the recordset as mentioned in URI's link, but this performs badly compared
to a where clause run on the server. Essentially I'd still personally go
down the rewrite route unless your tables are incredibly generic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Paging, Performance and ADODB

I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.

Paging, Performance and ADODB

I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.

Monday, March 12, 2012

Paging Technique

Questoin

I am using Sql Server 2000.

I have a table named Cities which has more than 2600000 records.

I have to display the records for a specific city page wise.

I don't want to compromise with performance.

Can anyone has the idea?

Waiting for your fruitful response.

Happy Day And Night For All

Muhammad Zeeshanuddin Khan

Hi Muhammad,

Check out this article. The idea behind is that you only fetch the records from the database that you display on the active pageIndex.

Succes!

Rutger van Hagen

Paging records on SQL server using derived tables : more question

I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
where PermitID like @.WorkType
order by WorkStart DESC
@.WorkType is a input parameter to the Stored proc and its value is
'01%'
When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.
However if I change the query manually to say:
where PermitID like '01%'
order by WorkStart DESC
The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
and at the beginning of the PLAN, there is a Bookmark Lookup which
includes the above where clause.
Whereas with the FIRST example , the BookMark Lookup in the beginning
doesn't show that where condition.
Can anyone help me better understand this anomaly?
TIA
===================================== I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Server
http://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...rbg (rbg.net@.gmail.com) writes:
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
> where PermitID like @.WorkType
> order by WorkStart DESC
> @.WorkType is a input parameter to the Stored proc and its value is
> '01%'
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
> However if I change the query manually to say:
> where PermitID like '01%'
> order by WorkStart DESC
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
> Can anyone help me better understand this anomaly?
That WHERE clause was not in your original post. Nor does the column
name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.
But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)
But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.
In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.
On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.
To test this theory, you can say:
EXEC your_sp @.WorkTyoe WITH RECOMPILE
if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||rbg,
SqlServerCentral.com has an article on paging. You will need to sign up to
get access.
http://www.sqlteam.com/item.asp?ItemID=26455
-- Bill
"rbg" <rbg.net@.gmail.com> wrote in message
news:1169732779.351525.299580@.s48g2000cws.googlegroups.com...
>I did use query plans to find out more. ( Please see the thread BELOW)
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
> where PermitID like @.WorkType
> order by WorkStart DESC
> @.WorkType is a input parameter to the Stored proc and its value is
> '01%'
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
> However if I change the query manually to say:
> where PermitID like '01%'
> order by WorkStart DESC
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
> Can anyone help me better understand this anomaly?
> TIA
> =====================================> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Server
> http://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =1000
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>

Paging records on SQL server using derived tables : more question

I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
where PermitID like @.WorkType
order by WorkStart DESC
@.WorkType is a input parameter to the Stored proc and its value is
'01%'
When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.
However if I change the query manually to say:
where PermitID like '01%'
order by WorkStart DESC
The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
and at the beginning of the PLAN, there is a Bookmark Lookup which
includes the above where clause.
Whereas with the FIRST example , the BookMark Lookup in the beginning
doesn't show that where condition.
Can anyone help me better understand this anomaly?
TIA
=====================================
I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Server
http://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,Conta
ctTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,Cont
actTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...rbg (rbg.net@.gmail.com) writes:
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
> where PermitID like @.WorkType
> order by WorkStart DESC
> @.WorkType is a input parameter to the Stored proc and its value is
> '01%'
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
> However if I change the query manually to say:
> where PermitID like '01%'
> order by WorkStart DESC
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
> Can anyone help me better understand this anomaly?
That WHERE clause was not in your original post. Nor does the column
name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.
But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)
But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.
In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.
On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.
To test this theory, you can say:
EXEC your_sp @.WorkTyoe WITH RECOMPILE
if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rbg,
SqlServerCentral.com has an article on paging. You will need to sign up to
get access.
http://www.sqlteam.com/item.asp?ItemID=26455
-- Bill
"rbg" <rbg.net@.gmail.com> wrote in message
news:1169732779.351525.299580@.s48g2000cws.googlegroups.com...
>I did use query plans to find out more. ( Please see the thread BELOW)
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
> where PermitID like @.WorkType
> order by WorkStart DESC
> @.WorkType is a input parameter to the Stored proc and its value is
> '01%'
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
> However if I change the query manually to say:
> where PermitID like '01%'
> order by WorkStart DESC
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
> Can anyone help me better understand this anomaly?
> TIA
> =====================================
> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Server
> http://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =1000
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,Conta
ctTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,Cont
actTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>

Paging records on SQL server using derived tables : more question

I did use query plans to find out more. ( Please see the thread BELOW)

I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :

where PermitID like @.WorkType
order by WorkStart DESC

@.WorkType is a input parameter to the Stored proc and its value is
'01%'

When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.

However if I change the query manually to say:

where PermitID like '01%'
order by WorkStart DESC

The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
and at the beginning of the PLAN, there is a Bookmark Lookup which
includes the above where clause.
Whereas with the FIRST example , the BookMark Lookup in the beginning
doesn't show that where condition.

Can anyone help me better understand this anomaly?

TIA
=====================================
I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Server

http://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:

CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)

SET @.rowsToRetrieve = (@.pageSize * @.currentPage)

SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'

EXEC(@.SQLSTRING)
RETURN
GO

When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.

This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the

@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize

Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.

Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)

Can anyone suggest what is wrong with my user paging logic???
TIA...rbg (rbg.net@.gmail.com) writes:

Quote:

Originally Posted by

I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
>
where PermitID like @.WorkType
order by WorkStart DESC
>
@.WorkType is a input parameter to the Stored proc and its value is
'01%'
>
When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.
>
However if I change the query manually to say:
>
where PermitID like '01%'
order by WorkStart DESC
>
The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
and at the beginning of the PLAN, there is a Bookmark Lookup which
includes the above where clause.
Whereas with the FIRST example , the BookMark Lookup in the beginning
doesn't show that where condition.
>
Can anyone help me better understand this anomaly?

That WHERE clause was not in your original post. Nor does the column
name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.

But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)

But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.

In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.

On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.

To test this theory, you can say:

EXEC your_sp @.WorkTyoe WITH RECOMPILE

if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rbg,

SqlServerCentral.com has an article on paging. You will need to sign up to
get access.

http://www.sqlteam.com/item.asp?ItemID=26455
-- Bill

"rbg" <rbg.net@.gmail.comwrote in message
news:1169732779.351525.299580@.s48g2000cws.googlegr oups.com...

Quote:

Originally Posted by

>I did use query plans to find out more. ( Please see the thread BELOW)
>
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
>
where PermitID like @.WorkType
order by WorkStart DESC
>
@.WorkType is a input parameter to the Stored proc and its value is
'01%'
>
When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.
>
However if I change the query manually to say:
>
where PermitID like '01%'
order by WorkStart DESC
>
The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
and at the beginning of the PLAN, there is a Bookmark Lookup which
includes the above where clause.
Whereas with the FIRST example , the BookMark Lookup in the beginning
doesn't show that where condition.
>
Can anyone help me better understand this anomaly?
>
TIA
=====================================
I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Server
>
http://msdn2.microsoft.com/en-us/library/ms979197.aspx
>
I wanted to use USER PAGING, thus I used the following code:
>
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
>
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
>
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
>
EXEC(@.SQLSTRING)
RETURN
GO
>
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
>
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
>
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
>
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
>
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
>
Can anyone suggest what is wrong with my user paging logic???
TIA...
>

|||You are right, I did not include the exact query since it has a whole
of joins and many where clauses in it.
I did not want to make the post very hard to read, hence I simplified
it.

In the Stored proc I am using a String variable @.SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@.SQLString).

Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.

so one select statement says:

Quote:

Originally Posted by

>From PermitMain inner join tbl_Permittee

on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
and PermitteeNumber = @.PermitteeNumber
and PermitMain.Boroughcode = @.Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC

THIS ONE RUNS FAST and RETURNS RESULTS.

The Other Select statement:

Quote:

Originally Posted by

>From PermitMain inner join tbl_Permittee

and PermitteeNumber = @.PermitteeNumber

and PermitMain.Boroughcode = @.Boro

order by WorkStart DESC

) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC

TAKES FOREVER to COMPLETE.

However IF I INCREASE the PAGESIZE from 600 to 800, BOTH QUERIES RETURN
RESULTS EQUALLY FAST.

Thanks for your help
On Jan 25, 5:49 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

rbg (rbg...@.gmail.com) writes:

Quote:

Originally Posted by

I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :

Quote:

Originally Posted by

where PermitID like @.WorkType
order by WorkStart DESC

Quote:

Originally Posted by

@.WorkType is a input parameter to the Stored proc and its value is
'01%'

Quote:

Originally Posted by

When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.

Quote:

Originally Posted by

However if I change the query manually to say:

Quote:

Originally Posted by

where PermitID like '01%'
order by WorkStart DESC

Quote:

Originally Posted by

Quote:

Originally Posted by

Can anyone help me better understand this anomaly?That WHERE clause was not in your original post. Nor does the column

name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.
>
But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)
>
But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.
>
In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.
>
On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.
>
To test this theory, you can say:
>
EXEC your_sp @.WorkTyoe WITH RECOMPILE
>
if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||PLEASE NOTE the variable was not WokStart but WORKTYPE.

So the query that takes very long looks like this:

Quote:

Originally Posted by

>From PermitMain inner join tbl_Permittee

and PermitteeNumber = @.PermitteeNumber

and PermitMain.Boroughcode = @.Boro

order by WorkStart DESC

) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC
================================================== =================
On Jan 26, 8:49 am, "rbg" <rbg...@.gmail.comwrote:

Quote:

Originally Posted by

You are right, I did not include the exact query since it has a whole
of joins and many where clauses in it.
I did not want to make the post very hard to read, hence I simplified
it.
>
In the Stored proc I am using a String variable @.SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@.SQLString).
>
Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.
>
so one select statement says:
>
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
and PermitteeNumber = @.PermitteeNumber
and PermitMain.Boroughcode = @.Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
>
) as T3
>
) as T4 order by WorkStart DESC
>
THIS ONE RUNS FAST and RETURNS RESULTS.
>
The Other Select statement:
>
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @.WokStart ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
>
and PermitteeNumber = @.PermitteeNumber
>
and PermitMain.Boroughcode = @.Boro
>
order by WorkStart DESC
>
) as T2 order by WorkStart ASC
>
) as T3
>
) as T4 order by WorkStart DESC
>
TAKES FOREVER to COMPLETE.
>
However IF I INCREASE the PAGESIZE from 600 to 800, BOTH QUERIES RETURN
RESULTS EQUALLY FAST.
>
Thanks for your help
On Jan 25, 5:49 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

rbg (rbg...@.gmail.com) writes:

Quote:

Originally Posted by

I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :

Quote:

Originally Posted by

Quote:

Originally Posted by

where PermitID like @.WorkType
order by WorkStart DESC

Quote:

Originally Posted by

Quote:

Originally Posted by

@.WorkType is a input parameter to the Stored proc and its value is
'01%'

Quote:

Originally Posted by

Quote:

Originally Posted by

When I use the above where clause, all the Sorts in the ESTIMATED Query
Execution plan show me a COST of 28%.

Quote:

Originally Posted by

Quote:

Originally Posted by

However if I change the query manually to say:

Quote:

Originally Posted by

Quote:

Originally Posted by

where PermitID like '01%'
order by WorkStart DESC

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Can anyone help me better understand this anomaly?That WHERE clause was not in your original post. Nor does the column

name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.

Quote:

Originally Posted by

On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.

Quote:

Originally Posted by

To test this theory, you can say:

Quote:

Originally Posted by

EXEC your_sp @.WorkTyoe WITH RECOMPILE

Quote:

Originally Posted by

if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||rbg (rbg.net@.gmail.com) writes:

Quote:

Originally Posted by

In the Stored proc I am using a String variable @.SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@.SQLString).
>
Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.
>
so one select statement says:
>...
>
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate

Quote:

Originally Posted by

>>From PermitMain inner join tbl_Permittee

on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @.WokStart ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
and PermitteeNumber = @.PermitteeNumber
and PermitMain.Boroughcode = @.Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
) as T3
) as T4 order by WorkStart DESC
>
TAKES FOREVER to COMPLETE.

Yes, but how does those variables makes into the dynamic SQL? You said
that you were using EXEC(), and EXEC() does not permit you to pass
parameters. Does your complete SQL string look something like:

DECLARE @.Workstart, ...

SELECT @.Workstart = '01%'
...
SEKECT * ...

Then you have preclsely the problem that I discussed in my previous
post. The optimizer has no clue of value @.workstart has, and will make
a blind assumption.

But you should not use EXEC(). Use sp_executesql instead. sp_executesql
permits you to pass parameters, and in this case the optimizer will
be able to use be parameter values for guidance.

See http://www.sommarskog.se/dynamic_sql.html#sp_executesql for
more details on sp_excecutsql.

If you want further help, please post your entire SQL batch, that is
the one that builds the dynamic SQL. It's a bit frustrating having to
guess what you are doing - and you get better answers that way.

Declare @.WorkType varchar(3), @.PermitteeNumber varchar(5), @.Boro
varchar(1)
Declare @.IssueDateFrom datetime, @.IssueDateTo datetime
Declare @.SortExpression varchar(50), @.SortOrder varchar(5)
Declare @.PageNum int, @.PageSize int

select @.PageNum = 2, @.PageSize = 1000
Declare @.rowsToRetrieve int
Declare @.SortOrderMirror varchar(4)

if @.SortOrder = 'ASC'
SET @.SortOrderMirror = 'DESC'
else
SET @.SortOrderMirror = 'ASC'

SET @.rowsToRetrieve = @.PageNum * @.PageSize

Declare @.TotalRows int

SET @.TotalRows = 1600

SET @.PageSize = @.TotalRows%@.PageSize

select @.WorkType = '01%', @.PermitteeNumber = '00180', @.Boro = 'M'
select @.IssueDateFrom = '01/24/2001', @.IssueDateTo = '01/24/2007'
select @.SortExpression = 'WorkStart', @.SortOrder = 'DESC'

Quote:

Originally Posted by

>From PermitMain inner join tbl_Permittee

and PermitteeNumber = @.PermitteeNumber

and PermitMain.Boroughcode = @.Boro

order by WorkStart DESC

) as T2 order by WorkStart ASC

) as T3

) as T4 order by WorkStart DESC
================================================== =====================
=
if I replace the above where clause from PermitType_ID like
@.WorkType to PermitType_ID like '01%'
The results are returned within 30 secs, else it takes forever.

Any Help will be grately appreciated.

TIA..
================================================== =====================
=
On Jan 26, 5:50 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

rbg (rbg...@.gmail.com) writes:

Quote:

Originally Posted by

In the Stored proc I am using a String variable @.SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@.SQLString).

Quote:

Originally Posted by

Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.

Quote:

Originally Posted by

so one select statement says:
...

Quote:

Originally Posted by

Quote:

Originally Posted by

>From PermitMain inner join tbl_Permittee

on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @.WokStart ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
and PermitteeNumber = @.PermitteeNumber
and PermitMain.Boroughcode = @.Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
) as T3
) as T4 order by WorkStart DESC

Quote:

Originally Posted by

TAKES FOREVER to COMPLETE.Yes, but how does those variables makes into the dynamic SQL? You said

that you were using EXEC(), and EXEC() does not permit you to pass
parameters. Does your complete SQL string look something like:
>
DECLARE @.Workstart, ...
>
SELECT @.Workstart = '01%'
...
SEKECT * ...
>
Then you have preclsely the problem that I discussed in my previous
post. The optimizer has no clue of value @.workstart has, and will make
a blind assumption.
>
But you should not use EXEC(). Use sp_executesql instead. sp_executesql
permits you to pass parameters, and in this case the optimizer will
be able to use be parameter values for guidance.
>
Seehttp://www.sommarskog.se/dynamic_sql.html#sp_executesqlfor
more details on sp_excecutsql.
>
If you want further help, please post your entire SQL batch, that is
the one that builds the dynamic SQL. It's a bit frustrating having to
guess what you are doing - and you get better answers that way.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Erland,

You were very much right regarding using sp_executesql stored proc for
executing dynamic queries.

Once I started using this:
Declare @.Params nvarchar(200)
SET @.Params = '@.WorkTypeSQL nvarchar(3), @.PermitteeNumberSQL
nvarchar(5), @.BoroSQL nvarchar(1)
exec sp_executesql @.Params, @.WorkType, @.PermitteeNumber, @.Boro

The performance improved and solved my original problem.

I have one more question though.
When I used Temp tables instead of derived tables, my performance
improved significantly. The query which takes 30 seconds using the
derived tables, takes only 11 seconds when using Temp tables.

So is it better to choose the Temp table solution over the derived
tables solution?

TIA

On Jan 29, 9:02 am, "rbg" <rbg...@.gmail.comwrote:

Quote:

Originally Posted by

Please find attached the complete query:
>
Declare @.WorkType varchar(3), @.PermitteeNumber varchar(5), @.Boro
varchar(1)
Declare @.IssueDateFrom datetime, @.IssueDateTo datetime
Declare @.SortExpression varchar(50), @.SortOrder varchar(5)
Declare @.PageNum int, @.PageSize int
>
select @.PageNum = 2, @.PageSize = 1000
Declare @.rowsToRetrieve int
Declare @.SortOrderMirror varchar(4)
>
if @.SortOrder = 'ASC'
SET @.SortOrderMirror = 'DESC'
else
SET @.SortOrderMirror = 'ASC'
>
SET @.rowsToRetrieve = @.PageNum * @.PageSize
>
Declare @.TotalRows int
>
SET @.TotalRows = 1600
>
SET @.PageSize = @.TotalRows%@.PageSize
>
select @.WorkType = '01%', @.PermitteeNumber = '00180', @.Boro = 'M'
select @.IssueDateFrom = '01/24/2001', @.IssueDateTo = '01/24/2007'
select @.SortExpression = 'WorkStart', @.SortOrder = 'DESC'
>
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @.WorkType ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
>
and PermitteeNumber = @.PermitteeNumber
>
and PermitMain.Boroughcode = @.Boro
>
order by WorkStart DESC
>
) as T2 order by WorkStart ASC
>
) as T3
>
) as T4 order by WorkStart DESC
================================================== =====================
=
if I replace the above where clause from PermitType_ID like
@.WorkType to PermitType_ID like '01%'
The results are returned within 30 secs, else it takes forever.
>
Any Help will be grately appreciated.
>
TIA..
================================================== =====================
=
On Jan 26, 5:50 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

rbg (rbg...@.gmail.com) writes:

Quote:

Originally Posted by

In the Stored proc I am using a String variable @.SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@.SQLString).

Quote:

Originally Posted by

Quote:

Originally Posted by

Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.

Quote:

Originally Posted by

Quote:

Originally Posted by

so one select statement says:
>...

Quote:

Originally Posted by

Quote:

Originally Posted by

Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate
>>From PermitMain inner join tbl_Permittee
on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @.WokStart ) and worktypeid is null
and ((IssueDate between @.IssueDateFrom and @.IssueDateTo) or
(EmergIssueDate between @.IssueDateFrom and @.IssueDateTo))
and PermitteeNumber = @.PermitteeNumber
and PermitMain.Boroughcode = @.Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
) as T3
) as T4 order by WorkStart DESC

Quote:

Originally Posted by

Quote:

Originally Posted by

TAKES FOREVER to COMPLETE.Yes, but how does those variables makes into the dynamic SQL? You said

that you were using EXEC(), and EXEC() does not permit you to pass
parameters. Does your complete SQL string look something like:

Quote:

Originally Posted by

DECLARE @.Workstart, ...

Quote:

Originally Posted by

SELECT @.Workstart = '01%'
...
SEKECT * ...

Quote:

Originally Posted by

Then you have preclsely the problem that I discussed in my previous
post. The optimizer has no clue of value @.workstart has, and will make
a blind assumption.

Quote:

Originally Posted by

But you should not use EXEC(). Use sp_executesql instead. sp_executesql
permits you to pass parameters, and in this case the optimizer will
be able to use be parameter values for guidance.

Quote:

Originally Posted by

Seehttp://www.sommarskog.se/dynamic_sql.html#sp_executesqlfor
more details on sp_excecutsql.

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Quote:

Originally Posted by

|||rbg (rbg.net@.gmail.com) writes:

Quote:

Originally Posted by

So instead of the derived tables, you did the SELECT TOP into the
temp tables? Or did you use the temp tables in any other way?

Assuming the first, this is one of those hairy questions of which the
short answer is "it depends". In most cases, it's more effecient to do
all in one query, rather than matierialising the intermediate results
in a temp table. This is because the optimizer may find ways to recast
the computation order without affecting the final result. But there are
always exceptions. In this particular case, the optimizer may not have
been able to find a shortcut. On the other hand, the temp table has
statistics, so when coming to the next query, the optimizer has more
information and may find a better plan.

I'm glad to hear that sp_executesql resolved your problems!

Paging records on SQL server using derived tables

CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =1000
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)

SET @.rowsToRetrieve = (@.pageSize * @.currentPage)

EXEC(@.SQLSTRING)
RETURN
GO

When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize

Can anyone suggest what is wrong with my user paging logic???
TIA...rbg (rbg.net@.gmail.com) writes:

Quote:

Originally Posted by

Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
>
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
>
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
>
Can anyone suggest what is wrong with my user paging logic???

I can't really say what happens, but if you study the query plans you may
do some discoveries.

But it goes to show that for paging there is not any single solution.
Aaron Bertrand has a fairly good overview on this site:
http://www.aspfaq.com/show.asp?id=2120.

Paging records on SQL server using derived tables

I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =10
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,Conta
ctTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,Cont
actTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...Hi
"rbg" wrote:

> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =10
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,Conta
ctTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,Cont
actTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,Cont
actTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>
Have you seen
http://databases.aspfaq.com/databas...-recordset.html
and
http://databases.aspfaq.com/databas...tic-paging.html
?
With SQL 2005 there are new functions that could help.
John

Friday, March 30, 2012

Parameter Problem

Tuesday, March 20, 2012

Pair of records

Paging, Soring, Filtering Already-written SPs Result

Paging, Performance and ADODB

Paging, Performance and ADODB

Paging, Performance and ADODB

Monday, March 12, 2012

Paging Technique

Paging records on SQL server using derived tables : more question

Paging records on SQL server using derived tables : more question

Paging records on SQL server using derived tables : more question

Paging records on SQL server using derived tables

Paging records on SQL server using derived tables

Page Numbers in Excel

Blog Archive

About Me