[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