473,722 Members | 2,353 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Paging records on SQL server using derived tables : more question

rbg
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,Comp anyName,Contact Name,ContactTit le from
( SELECT TOP '+ CAST(@pageSize as varchar(10)) +
'CustomerId,Com panyName,Contac tName,ContactTi tle from
( SELECT TOP ' + CAST(@rowsToRet rieve as varchar(10)) +
'CustomerID,Com panyName,Contac tName,ContactTi tle FROM
( SELECT TOP ' + CAST(@rowsToRet rieve as varchar(10)) +
'CustomerID,Com panyName,Contac tName,ContactTi tle 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%@Pag eSize

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...

Jan 25 '07 #1
8 2805
rbg (rb*****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 25 '07 #2
rbg,

SqlServerCentra l.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" <rb*****@gmail. comwrote in message
news:11******** **************@ s48g2000cws.goo glegroups.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,Comp anyName,Contact Name,ContactTit le from
( SELECT TOP '+ CAST(@pageSize as varchar(10)) +
'CustomerId,Com panyName,Contac tName,ContactTi tle from
( SELECT TOP ' + CAST(@rowsToRet rieve as varchar(10)) +
'CustomerID,Com panyName,Contac tName,ContactTi tle FROM
( SELECT TOP ' + CAST(@rowsToRet rieve as varchar(10)) +
'CustomerID,Com panyName,Contac tName,ContactTi tle 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%@Pag eSize

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...

Jan 26 '07 #3
rbg
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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumbe r
and PermitMain.Boro ughcode = @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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumbe r

and PermitMain.Boro ughcode = @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...@sommars kog.sewrote:
rbg (rbg....@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, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Jan 26 '07 #4
rbg
PLEASE NOTE the variable was not WokStart but WORKTYPE.

So the query that takes very long looks like this:

Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart" ,
tbl_Permittee.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WorkType ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumbe r

and PermitMain.Boro ughcode = @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:
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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumbe r
and PermitMain.Boro ughcode = @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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumbe r

and PermitMain.Boro ughcode = @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...@sommars kog.sewrote:
rbg (rbg....@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, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Jan 26 '07 #5
rbg (rb*****@gmail. com) writes:
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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumbe r
and PermitMain.Boro ughcode = @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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '07 #6
rbg
Please find attached the complete query:

Declare @WorkType varchar(3), @PermitteeNumbe r 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 @SortOrderMirro r varchar(4)

if @SortOrder = 'ASC'
SET @SortOrderMirro r = 'DESC'
else
SET @SortOrderMirro r = 'ASC'

SET @rowsToRetrieve = @PageNum * @PageSize

Declare @TotalRows int

SET @TotalRows = 1600

SET @PageSize = @TotalRows%@Pag eSize

select @WorkType = '01%', @PermitteeNumbe r = '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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WorkType ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumbe r

and PermitMain.Boro ughcode = @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...@sommars kog.sewrote:
rbg (rbg....@gmail. com) writes:
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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumbe r
and PermitMain.Boro ughcode = @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.

Seehttp://www.sommarskog. se/dynamic_sql.htm l#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.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Jan 29 '07 #7
rbg
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), @PermitteeNumbe rSQL
nvarchar(5), @BoroSQL nvarchar(1)
exec sp_executesql @Params, @WorkType, @PermitteeNumbe r, @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:
Please find attached the complete query:

Declare @WorkType varchar(3), @PermitteeNumbe r 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 @SortOrderMirro r varchar(4)

if @SortOrder = 'ASC'
SET @SortOrderMirro r = 'DESC'
else
SET @SortOrderMirro r = 'ASC'

SET @rowsToRetrieve = @PageNum * @PageSize

Declare @TotalRows int

SET @TotalRows = 1600

SET @PageSize = @TotalRows%@Pag eSize

select @WorkType = '01%', @PermitteeNumbe r = '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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate>From PermitMain inner join tbl_Permitteeon PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WorkType ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))

and PermitteeNumber = @PermitteeNumbe r

and PermitMain.Boro ughcode = @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...@sommars kog.sewrote:
rbg (rbg....@gmail. com) writes:
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.P ermittee_name as PermitteeName, PermitteeNumber ,
PermitType_ID as "Type",
InspectionDistr ict,
PermitStatus,
IssueDate
>>From PermitMain inner join tbl_Permittee
on PermitMain.Perm itteeNumber = tbl_Permittee.P ermittee_Number
and (tbl_Permittee. Permittee_name_ flag = 'd' or
tbl_Permittee.P ermittee_name_f lag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumbe r
and PermitMain.Boro ughcode = @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.
Seehttp://www.sommarskog. se/dynamic_sql.htm l#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.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Jan 29 '07 #8
rbg (rb*****@gmail. com) writes:
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?
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!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 29 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2732
by: ck388 | last post by:
For some reason when I enable the callback feature of the gridview I still get a page refresh, that is it seems like there is a postback that occurs, not a callback which is just supposed to update not the whole page, but a portion of the page. Strangely enough the URL below http://beta.asp.net/QUICKSTARTV20/aspnet/doc/ctrlref/data/gridview.aspx (VB GridView Paging and Sorting Callbacks example)
2
1352
by: Steven K | last post by:
Hello, I am converting an ASP page to ASP.Net. In one page, I am using record paging. I have read that while the DataGrid control has paging, it can tax resources. So I did a Google and found and example of using the Repeater control (http://aspnet.4guysfromrolla.com/articles/091003-1.aspx). My question is at approximately how many records does it make sense to not use the DataGrid control, or should I always use something like the...
6
1813
by: Shawn | last post by:
Hi. I already have a datagrid where I'm using paging. I have a stored procedure that fills a temp table with 200-500 rows and then sends back 10 records at the time. When I go to page 2 the SP fills the temp table again and returns rows 10-19. The temp table is dropped after each call to the SP, so it has to be created and filled every time the user changes page in the datagrid. My question is this: Would it be more efficient to...
24
19910
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every renewal in the history of the policyholder. The information is in 2 tables, policy and customer, which share the custid data. The polno changes with every renewal Renewals in 2004 would be D, 2005 S, and 2006 L. polexpdates for a given customer...
2
6448
by: farhad13841384 | last post by:
Hi , I Hope You fine. I have some problem with this code for paging in asp.net this bottom code work correctly without any error but when I try to place separate code in .VB file then error is begin and occured .I want to separate this code and compiling .vb code using VBC.exe later .(bin/paging.dll) when do it like me so you retrive only < Previous Page Next Page > in your web browser and you don't retrive list Of data in your web browser....
0
3390
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
1
4146
by: rbg | last post by:
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 =1000 ) AS
8
1451
by: Donald Adams | last post by:
I heard in an online video by Scott Gu, that there is an option to have the asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than pull all the data from the database and do it on the web server. I've been unable to find any good documentation on this. I saw a property to turn on paging if the select supports it, but that did not give me enough information to complete my task. Does anyone know where I can find...
2
1690
by: suneelid2000 | last post by:
Actually i am really confused regarding paging activity I want to implement paging in asp.net but i am not able to take out the idea regarding how to implement the concept for large databases where the tables r containing 1000s of records Problem is:I cannot save the dataset in session as it will b too costly and all the time i cannot access the database as database transasction require costly operations
0
8861
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9383
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9088
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8051
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5995
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.