Page Numbers in Excel: tables

Showing posts with label tables. Show all posts

Wednesday, March 21, 2012

Parallel execution of DTS steps.

Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
MaxYes, you can. Basically, tasks run in parallel, unless you have set up
workflows that force them to be run serially. That said, if your source is
the same for all of your data pumps, then the process gets serialized, since
the connection is in use. In that case, create multiple connections, each
of which point to the same server.
For example, if you are pumping from ServerA to ServerB, create, say, 4
ServerA connections - ServrA1, ServerA2, etc. and the same goes for ServerB.
Then, make sure your pumps use the different connections.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eobZWSBWGHA.5044@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
Max|||Then you will have to use a different connection for each datapump.
AMB
"Maxwell2006" wrote:
> Hi,
>
> I have a DTS package that exports 50 database tables from SQL Server to
> Oracle.
> At this point the DTS package runs steps after each other (serial
> execution). Since both servers are extremely powerful, the can handle more
> than one concurrent DTS step at a time.
>
> Is there any way to have DTS execute five or six steps in parallel fashion?
>
> Any help would be appreciated,
> Max
>
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

Tuesday, March 20, 2012

Paradox table import in SQL Server 2000

I need to import any paradox tables in SQL Server 2000, but i'm having
troubles with char conversion:
Does anyone have any idea about why all accented chars in source paradox
table turn into other chars in the new SQL Table:
in paradox turns into in SQL
" " " " " "
" " " " " "
" " " " ? " "
" " " " " "
" " " " " "
Any hints on what should i trim to correcty this behavior?
Thanks
Mario
you need to pick another collation. What collation is DTS automatically
selecting? It looks like you should be using French.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:cguq57$vv4$1@.news.ngi.it...
> I need to import any paradox tables in SQL Server 2000, but i'm having
> troubles with char conversion:
> Does anyone have any idea about why all accented chars in source paradox
> table turn into other chars in the new SQL Table:
> in paradox turns into in SQL
> " " " " " "
> " " " " " "
> " " " " ? " "
> " " " " " "
> " " " " " "
> Any hints on what should i trim to correcty this behavior?
> Thanks
> Mario
>
|||Hello Hilary,
i couldn't find a way to see which collation is my DTS automatically
selecting, i can only imagine it's Latin General since i've changed nothing
in default settings and i'm in italy.
Anyway it looks more like it's a problem with settings on OEM/ANSI
translation settings, but even in this case i couldn't find a way to set
such a setting.
Mario Cappa
"Hilary Cotter" <hilaryk@.att.net> ha scritto nel messaggio
news:ezjkYZojEHA.3724@.TK2MSFTNGP11.phx.gbl...
> you need to pick another collation. What collation is DTS automatically
> selecting? It looks like you should be using French.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:cguq57$vv4$1@.news.ngi.it...
>
|||how are you importing the table? Is this through a DSN? If so what collation
sequence are you using. I think you should be using ANSI.
Also check the collation of the table which DTS created. Do a sp_help
TableName using Query Analyzer.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:cgvh2s$848$1@.news.ngi.it...
> Hello Hilary,
> i couldn't find a way to see which collation is my DTS automatically
> selecting, i can only imagine it's Latin General since i've changed
nothing[vbcol=seagreen]
> in default settings and i'm in italy.
> Anyway it looks more like it's a problem with settings on OEM/ANSI
> translation settings, but even in this case i couldn't find a way to set
> such a setting.
> Mario Cappa
> "Hilary Cotter" <hilaryk@.att.net> ha scritto nel messaggio
> news:ezjkYZojEHA.3724@.TK2MSFTNGP11.phx.gbl...
paradox
>
|||I'm using ANSI on SQL but i'm not sure the setting is the same on Paradox
table
Table is imported through Paradox 5.x OLE DB drivers and collation sequence
results as Latin1_General_CI_AS
"Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
news:Our0dmqjEHA.556@.tk2msftngp13.phx.gbl...
> how are you importing the table? Is this through a DSN? If so what
collation[vbcol=seagreen]
> sequence are you using. I think you should be using ANSI.
> Also check the collation of the table which DTS created. Do a sp_help
> TableName using Query Analyzer.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:cgvh2s$848$1@.news.ngi.it...
> nothing
automatically[vbcol=seagreen]
having
> paradox
>
|||how about trying the international setting on your dsn?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:ezhKiuqjEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm using ANSI on SQL but i'm not sure the setting is the same on Paradox
> table
> Table is imported through Paradox 5.x OLE DB drivers and collation
sequence[vbcol=seagreen]
> results as Latin1_General_CI_AS
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
> news:Our0dmqjEHA.556@.tk2msftngp13.phx.gbl...
> collation
set
> automatically
> having
>
|||I solved my problems after i could find paradox tables native language and
change it to Western Europe ANSI
Thanky you for your suggestions
Mario
"Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
news:%23HjsoMwjEHA.2340@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> how about trying the international setting on your dsn?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:ezhKiuqjEHA.704@.TK2MSFTNGP09.phx.gbl...
Paradox[vbcol=seagreen]
> sequence
automatically
> set
>

Paging with Temporary Tables

I am searching for information on paging large datasets, and have foundsome that involve creating temporary tables in the database. Before I head off and implement something, I have a number of issuesI'd like to bounce around here.
1. An example I found on MSDN involves creating a temporary table,copying relevant columns to the row in the temp table. Why dothis, rather add the source tables primary keys into the temp table,and do a join? Example; browsing Products Catalog which iscategorised into hierarchies. The MSDN version would have a temptable created with a incrementing field which is used for the paging,and then a number of fields are also copied from the products table tothe temp table - my question is why not simply copy the product primarykey into the temp table, and then join?
2. In real life, do people allow each user to create their owntemporary tables? If I have 1000 concurrent users, all wishing toperform a page-based browse, I would be creating 1000 new temporarytables. Do people consider default temp tables, that is, creatinga default temporary table for browsing each category in the productstable, for example?
3. Do you have any advice/tips for this type of problem?
Thanks!
JR.
1) I think I know what you are talking about. However, I would like to point you to another techniquethat I developed.
2) It depends what type of temporary table. The one that I use is memory resident and since it is only one column as such in memory, it's a very small trade off in resources. If they are creating the "physical" version of the temporary table, then yes, I would consider an idea that you are toying with. Essentially (just bear with me), you would just create a cross reference table to state that page X has these records... just like my inner join technique. I hate circles but at least you can see that I highlighted the most normalized way to put the pagings rather than create a whole new "physical" table to contain that information.
3) I can't think of anything other than what I just mentioned for the time being.
Regards,
Justin|||

You could try this. It uses a table variable. It takes 2 pareameters which should be self explanitory. I have tested it with a 50,000 row zipcode database and it seems to work quite well.
<code>
-- FOR MSSQL 2000

CREATE PROCEDURE dbo.Paging_sp

@.Page int,
@.RecsPerPage int

SET NOCOUNT ON

DECLARE @.TableVar table (
ZipID int identity(1,1) PRIMARY KEY,
ZipCode varchar(5) NOT NULL,
City varchar(28) NOT NULL,
State varchar(50) NOT NULL )

Insert Into @.TableVar ( ZipCode, City, State )
Select ZIPCODE, CITY, STATE
From ZipCodes
ORDER BY STATE

DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)

SELECT ZipID, ZipCode, City, State,
MoreRecords = (
SELECT COUNT(*)
FROM @.TableVar TI
WHERE TI.ZipID >= @.LastRec),
TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM @.TableVar )
FROM @.TableVar
WHERE ZipID > @.FirstRec AND ZipID < @.LastRec

SET NOCOUNT OFF

GO
</code>

Of course there is always this:http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

|||Justin,
I thank you for your response. I am trying to consider andimplement your example. I looked around at many examples, and allseem to be confusingly different in implementation. My mainconcern is how well these implementations work with many concurrentusers, with a single web server and database server - theimplementation will not sit on a data center, so it needs to beperformant. From what I am seeing in the posts, all pagingsolutions come with their own caveats. I will get back to youJustin, regarding your own version. I am assuming I need to putit into a stored proc, but my db skills are not like they used tobe.
Thanks again,
Jr.
|||Hi thanks for this mail. I am working with Justins as a test run, but will keep this in mind.
Thank you.
Jr.
|||This is a silly question I know, but I have a Products table, aCategoryProducts table, and a Category table. To page, I willneed to do a join across these tables:
select a.ProductId, a.Name
from
soundleaf_products a,
soundleaf_categoryProducts b
where
b.categoryid = @.CategoryID and
b.productid = a.productid
This is really simple SQL, but from a performance place, I dont know ifthis is performant or not. I dont know how to test performance ofSQL, so any opinions on this would be very helpful.
Jr.
|||

I found you can speed up Justins solution by avoiding using a cursor like such. Especially on the lower page numbers
<code>
--## FIRST Create the Very Large Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VeryLargeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VeryLargeTable]
GO

CREATE TABLE [dbo].[VeryLargeTable] (
[VLTID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OtherData] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

--## SECOND Load some data
--## As we are adding 1 million records to the database..
--## please be patient as it could take some time.
--## If you already have a very large table to work from
--## you can use use that instead.

DECLARE @.x int
SET @.x = 1

WHILE @.x < 1000000
BEGIN
INSERT VeryLargeTable ( FirstName, LastName, OtherData )
VALUES ( 'John' + ' ' + CAST ( @.x AS varchar(100) ), 'Doe' + ' ' + CAST ( @.x AS varchar(100) ), 'YAK Stuff' + ' ' + CAST ( @.x AS varchar(100) ) )
SET @.x = @.x + 1
END

GO
--## FIFTH Create our Fast NON Cursor Procedure
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast_New

@.Page int,
@.RecsPerPage int,
@.TotalPages int OUTPUT

set nocount on

SELECT @.TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM VeryLargeTable )

create table #tempTable(pk int Primary Key)

Declare @.execStr varchar(2000)

Set @.execStr = 'Select top ' + convert(varchar,@.RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@.RecsPerPage*(@.page)) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc'

Insert into #tempTable
exec(@.execStr)

Select *
From veryLargeTable A
Join #tempTable B
On A.VLTID = B.pk

drop table #tempTable

set nocount off

GO
--## Execute the procedure
DECLARE @.Total int

EXEC dbo.TestVeryLargePaging_Fast_New 4, 500, @.Total OUTPUT

PRINT @.Total

GO
</code>
This procedure uses basically the same approach as Justins but without using a cursor.
JB

|||

A little help fromhttp://www.seventhnight.com/ and now it is screamin fast!
<code>
ALTER PROCEDURE dbo.TestVeryLargePaging_Fast_New

@.Page int,
@.RecsPerPage int,
@.TotalPages int OUTPUT

SELECT @.TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM VeryLargeTable )

Declare @.execStr varchar(2000)

Set @.execStr = 'Select Z.* From veryLargeTable Z Inner Join (Select top ' + convert(varchar,@.RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@.RecsPerPage*(@.page)) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc) Y On Z.VLTID = Y.VLTID order By Z.VLTID'
exec(@.execStr)
go

</code>

|||JB, thanks for writing the above - I intend to play with what you'vewritten. Before I do I want to modify the original justinproduced, to take in a PageIndex rather than a start row. I amnot so good at SQL, and tried the following:
CREATE PROCEDURE [dbo].[PagingQueryProductsByCategory]
(
@.CategoryId int = 0,
@.PageIndex int = 1,
@.RowCount int = 10,
@.TotalRowCount int OUTPUT
)
AS
Declare @.TotalRows int, @.StartPosition int
Declare @.PK int
DECLARE @.tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)
Set RowCount @.RowCount
SELECT @.StartPosition = (((@.PageIndex - 1) * @.RowCount) + 1)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @.CategoryID and
b.productid = a.productid
Order By a.productid
Open PagingCursor
Fetch Relative @.StartPosition From PagingCursor Into @.PK
while (@.RowCount <> 0) And (@.@.Fetch_Status = 0)
begin
Insert Into @.tmpTable (PK)
Values (@.PK)
Fetch Next From PagingCursor Into @.PK
Set @.RowCount = @.RowCount - 1
end
Close PagingCursor
Deallocate PagingCursor
Select Products.* From Products
Join @.tmpTable temp ON Products.ProductID = temp.PK
Order By Products.ProductID
Set RowCount 0
GO
If I execute it with PageIndex = 1, I get back say 10 rows. If Iexecute it with PageIndex > 1, I get nothing returned. I thinkit could be to do with the way the curso works with the Relativekeyword - I dont know how cursors work. Any advice?
Thanks.
JR.
|||

<code>
--## Make sure this query returns a LARGE resultset of ProductID's
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @.CategoryID and
b.productid = a.productid
Order By a.productid

-## If it doesn't tweek it until it does.

--## NEXT Ceate THIS procedure
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast

@.Page int,
@.RecsPerPage int,
@.TotalPages int OUTPUT

DECLARE @.FirstRec int
SELECT @.FirstRec = (@.Page -1) * @.RecsPerPage + 1

DECLARE @.RowCount int
SELECT @.RowCount = @.RecsPerPage

SELECT @.TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM VeryLargeTable )

DECLARE @.PK int
DECLARE @.tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR

--## REPLACE THIS WITH YOUR RESLUT SET FROM ABOVE IF NEEDED
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @.CategoryID and
b.productid = a.productid
Order By a.productid

OPEN PagingCursor
FETCH RELATIVE @.FirstRec FROM PagingCursor INTO @.PK

WHILE (@.RowCount <> 0) And (@.@.Fetch_Status = 0)
BEGIN
INSERT INTO @.tmpTable (PK)
VALUES (@.PK)

FETCH NEXT FROM PagingCursor INTO @.PK
SET @.RowCount = @.RowCount - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

Select Products.* From Products p
Join @.tmpTable temp ON p.ProductID = temp.PK
Order By Products.ProductID

--## The above procedure take 3 parameters.
--## @.Page -- This is the page number -- 1st, 2nd, third etc...set it to 1, or 2, or 3 etc...
--## @.RecsPerPage -- This is the number of results perpage that you want returned. Set it to 25 or 50 or 10 etc...
--## @.TotalPages -- This is an output parameter that tell you how many pages there are bases on the settings of @.Page and PRecsPerPage.

--## TO EXECUTE THE PROCEDURE RUN THIS AND PUT YOUR PARAMETERS IN ORDER
DECLARE @.Total int
EXEC dbo.TestVeryLargePaging_Fast 2, 50, @.Total OUTPUT
PRINT 'Total Pages: ' + CAST(@.Total as varchar(50))

GO

</code>

|||

JRieggle wrote:

I think it could be to do with the way the curso works with the Relative keyword - I dont know how cursors work. Any advice?

Cursors forces a database to keep data in one order instead of the Rows and Columns of the relational model, a Cursor with more than three FETCH can confuse SQL Server query processor, as Peter Gulutzan who tested and watched said you have asked me to do something I am not equipped to do so you think I need five loops but I am going to take twenty loops. But paging is not easy to create. Hope this helps.

|||

JBelthoff wrote:
A little help fromhttp://www.seventhnight.com/ and now it is screamin fast!
<code>
ALTER PROCEDURE dbo.TestVeryLargePaging_Fast_New
@.Page int,
@.RecsPerPage int,
@.TotalPages int OUTPUT
as
SELECT @.TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM VeryLargeTable )
Declare @.execStr varchar(2000)
Set @.execStr = 'Select Z.* From veryLargeTable Z Inner Join (Select top ' + convert(varchar,@.RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@.RecsPerPage*(@.page)) +
'VLTID from veryLargeTable Order By VLTID) A order by VLTID desc) Y OnZ.VLTID = Y.VLTID order By Z.VLTID'
exec(@.execStr)
go

</code>

Hmm... alright! I must just test it out with some unit tests and that tonight.
The only thing that I'm a bit doubtful about "standard paging" withSeventhnight's solution is with sorting. It is generally an expensiveoperation but the real question is whose is the most expensive of themall? Smile [:)]

Other than talking in terms of raw speed, there is onefeature you sacrifice if you go with his solution is that you can'thave dynamic sorting (ie: you would like to be able to sort on itemsfrom the datagrid columns).
|||I have a question regarding sorting and reallife applications. Lets say I want to browse a certain product category, and I want tooffer a number of different sorting orders (A-Z, Z-A, Price High-Low,Price Low-High, etc...like Amazon). Based on the scripts in thismessage thread, where would the ordering come in? I am trying to workout if building the SQL in the business layer will be any better thanbuilding it in a stored procedure? If building in a stored procedure,how would it be possible to support all of these different routines?
Thanks for any ideas
jr.
|||

<code>
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast_New

@.Page int,
@.RecsPerPage int,
@.OrderCoumn varchar(100),
@.TotalPages int OUTPUT

SELECT @.TotalPages = ( SELECT COUNT(*)/@.RecsPerPage + 1 FROM VeryLargeTable )

Declare @.execStr varchar(2000)

</code>

|||JB,
Just writing to ask some questions about your SQL. I rewrote it to make it easier to read:
Select Z.* From Products Z Inner Join
(Select top 20 A.ProductID
from (
Select top 20 B1.ProductID
from Products B1, categoryProducts B2
where B2.CategoryID = 1 and B1.ProductID = B2.ProductID
Order By B1.Name
) A
order by A.Name desc
) Y On Z.ProductID = Y.ProductID
order by Z.Name
Lets say the blue is the core search. In this search the Order Byuses the Name, rather than the Id. The reason I am doing thishere, is because if I use the query as it was in your previous post,indexing to a particular page, I noticed only the results in that pageare ordered, which isnt what I want. I want to order the completeset, then page. This is why I moved the Order into thecore. This then produces a table of n results. The top 20 would have been top 10 * PageIndex which is set to 2, but Icouldnt get it to work for this example, and substituted it for thepage index. This inner query will create a resultset PageIndex *RowCount long. If the result count is only 20, and the pageindexis 10, then the number of results created in the core query will be200. The outer query in green confuses me, because I dont knowwhy the order by clause is there. It seems to me that all thatneeds to be done is take the top 20 off of the inner query resultlist. However, I see the reason for the order by is to invert thelist, since the page we want is at the end, and this ordering invertsthe complete resultset. Originally this query was orderingcompletely on the ProductID until the veryu outer query (Z.ProductID isnow changed to Z.Name).
One concern I have with this query is that I need to order the itemsagain on the very outer query - is this because the indexer will matchthe IDs by going down the ProductID key field, which will be in order?If I dont have the order by Z.Name, the items come out in ProductIDorder. My last point was to say the reason I posted this is tokeep along with this thread, a version of the query that uses adifferent column to sort by.
Thanks JB,
jr.

Monday, March 12, 2012

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!

Wednesday, March 21, 2012

Parallel execution of DTS steps.

Tuesday, March 20, 2012

Paradox table import in SQL Server 2000

Paging with Temporary Tables

Monday, March 12, 2012

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

Page Numbers in Excel

Blog Archive

About Me