jwcolby
jwcolby at colbyconsulting.com
Wed Apr 30 15:19:00 CDT 2008
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 > >