[dba-VB] vb.net - Updates

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
> 
> 



More information about the dba-VB mailing list