Eric Barro
ebarro at verizon.net
Wed Apr 30 13:50:38 CDT 2008
try to add (nolock) after the table names in your select queries... From: Shamil Salakhetdinov <shamil at smsconsulting.spb.ru> Date: 2008/04/30 Wed PM 01:22:08 CDT To: "'Discussion concerning Visual Basic and related programming issues.'" <dba-vb at databaseadvisors.com> Subject: Re: [dba-VB] vb.net - Updates John, Yes, I do realize it returns 96K records... I'm sorry - I should have not used anything except PK in the query fields' list - i.e. I wanted to run this query: 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] I expected it will use only the index and therefore should run rather quickly producing first results instantly and finishing within 3(? three ?) seconds? Here I have tested similar query against a table with only 1,374,163 rows but queried every 10th row, i.e. it returned 137K rows, and this query finished in 3 seconds: SELECT RowNum, CompetitionresultId from ( select Cast(ROW_NUMBER() over (ORDER BY [CompetitionresultId]) as int) as RowNum, CompetitionresultId from [CompetitionresultArc]) s where (s.RowNum % 10) = 1 union all Select NULL as RowNum, Max(CompetitionresultId) as CompetitionresultId from [CompetitionresultArc] Thanks. -- 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:33 PM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] vb.net - Updates It started returning results in about 12-13 seconds. You are asking for 96 thousand result rows you realize. This database is 96 million rows, so every thousandth row is 96K rows. It is still processing at 9 minutes. I'll email the final execution time whenever the query finishes. This is fascinating because I pull just the fields into an ADO.Net table for 100K rows in just a second or two. Of course these are consecutive rows, it is not scanning the entire table. 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