Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Wed Apr 30 16:00:45 CDT 2008
<<< Again, that is in SSMS, directly running a query and "displaying" it in a query pane. Was that your intention? >>> Yes, it was, but I expected you'll get results much quicker... Could that be that you do not have (clustered)index on your PK field? Can you: Right-Click on table name -> Script Table as -> CREATE to -> New Query Window => I'm getting the following for your simulated table. CREATE TABLE [dbo].[Names]( [PKID] [int] IDENTITY(1,1) NOT NULL, [OWNERNAME] [nvarchar](255) NOT NULL, [FName] [nvarchar](50) NULL, [MName] [nvarchar](50) NULL, [LName] [nvarchar](50) NULL, [NamePrefix] [nvarchar](50) NULL, [NameSuffix] [nvarchar](50) NULL, [Gender] [nvarchar](50) NULL, [ParsedName] [nvarchar](255) NULL, CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED ( [PKID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Also if you select query's text in SSMS query window, and then Ctrl+L then you'll get query execution plan - here as the last step with 99% cost I'm getting: "Clustered Index Scan" IOW the query doesn't scan table's data at all - it just scans the index... Thanks, -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, May 01, 2008 12:19 AM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] vb.net - Updates Shamil, > You have a very good PC, lucky man! :) As I mentioned elsewhere, I come from the "throw more hardware at it" school of Engineering. ;-) I have another machine (Azul) with a quad core (AMDPhenom) 2.2ghz, 8 gigs ram, Win2k3 x64 SQL Server 2005 x64. That machine is MUCH faster in many cases. I do not have this large database over on that machine, and it would take me hours to get it over there so i can't test this query on that, at least today. that database has not been compacted recently and is about 300 Gigs with 136 gigs free space. I will compact it tonight and then copy the smaller file to Azul. As for your new query, with the NoLocks in place it returned first results in only 8 seconds as opposed to 14 before, but it still took 13:59 to pull the entire result set. Again, that is in SSMS, directly running a query and "displaying" it in a query pane. Was that your intention? John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > Thank you, John, > > You have a very good PC, lucky man! :) > > As I wrote already I (I'm sorry) did post incorrect query - I wanted to run > this one: > > SELECT RowNum, PKID from ( select > Cast(ROW_NUMBER() over (ORDER BY [PKID]) as int) as RowNum, PKID > from [Names]) s > where (s.RowNum % 1000) = 1 > union all > Select NULL as RowNum, Max(PKID) as PKID from [Names] > > It will also return 96K rows for your huge table, but it's expected (here) > it will get first results instantly, and it will finish within just several > (3?) seconds... > > Please prove am I wrong with that bright above expectations on the execution > time of the fixed version of the query?... > > Thank you. > > -- > Shamil > > -----Original Message----- > From: dba-vb-bounces at databaseadvisors.com > [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Wednesday, April 30, 2008 9:41 PM > To: Discussion concerning Visual Basic and related programming issues. > Subject: Re: [dba-VB] vb.net - Updates > > Shamil, > > It took Stonehenge (2.8ghz AMD x2, 4 gigs Ram, Windows x32, SQL Server > x32) 13:51 to pull 96,417 rows for the result set. > > John W. Colby > www.ColbyConsulting.com > > > Shamil Salakhetdinov wrote: >> Hi John, >> >> May I ask you to run this query (e.g. in MS SQL Management Studio) against >> your huge db table? >> >> --- cut here --- >> >> SELECT RowNum, PKID from ( select >> Cast(ROW_NUMBER() over (ORDER BY [PKID]) as int) as RowNum, >> PKID, OWNERNAME, FName, MName, LName, NamePrefix, >> NameSuffix, Gender from [Names]) s >> where (s.RowNum % 1000) = 1 >> union all >> Select NULL as RowNum, Max(PKID) as PKID from [Names] >> >> --- cut here --- >> >> M.B. [Names] have to be substituted with the name of your table - what is > it >> BTW? >> >> This query returns PKID of every 1000th row sorted by PKID + MAX(PKID).... >> >> I'm curious to know how much time approx. this query will take to > execute... >> Thank you. >> >> -- >> Shamil >> >> P.S. BTW, here is a useful for your case article on multi-threading >> (practical solution) with good introduction - >> > http://www.dotnetjunkies.com/Tutorial/D7E688B8-0BDD-4D44-9A0F-4CD26FB35F51.d >> cik >> >> Sorry, I can't react promptly on your messages in this discussion thread: > a >> lot of custom work here, long weekends and our time zones difference but I >> do continue to work in this direction and if I get some useful results I >> will post them here... >> >> P.P.S. This book could be also useful: >> >> C# Threading Handbook >> by Tobin Titus et al. >> >> ISBN:1861008295 >> >> APress, LLC C 2004 (288 pages) >> >> This book addresses the fundamental units of Windows and .NET >> programming-threads. Coverage includes how .NET applications are executed, >> the life cycle of a thread in .NET, how the .NET Framework uses threads, > and >> more. >> >> >> _______________________________________________ >> dba-VB mailing list >> dba-VB at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-vb >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > > _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com