jwcolby
jwcolby at colbyconsulting.com
Wed Apr 30 14:55:15 CDT 2008
Well, I do not know what you want me to do with the result. To just open a query window in SSMS and execute it in there I get pretty much the same result. First results in 14 seconds and ten minutes and counting. I don't have "just the PK" indexed, at least not intentionally and visibly. I am now using cover indexes that index the PKID and one or more fields that I am interested in. Whether there is a PKID index behind the scenes I don't know. John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > 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 > >