[dba-VB] vb.net - Updates

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Wed Apr 30 16:07:55 CDT 2008


John,

This ORDER BY used here to get ROW_NUMBER is/was expected as very speedy
"virtual" zero cost operation because I thought/expected your PK (PKID) was
a (clustered) index...

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, May 01, 2008 12:33 AM
To: ebarro at verizon.net; Discussion concerning Visual Basic and related
programming issues.
Subject: Re: [dba-VB] vb.net - Updates

Shamil will have to do that.  I am just running the tests.

8-)

In fact I am not sure why the OrderBy anyway.  The PKID is a PK and is 
in order anyway, though you couldn't be sure of that in the generic sense.

John W. Colby
www.ColbyConsulting.com


Eric Barro wrote:
> How about this approach?
> 
> 1. Create a temp table with the output fields you want in the final
result.
> 2. INSERT the results of the first SELECT statement into the temp table.
> 3. INSERT the results of the second SELECT statement into the same temp
table.
> 4. Run the SELECT statement on the temp table.
> 
> I see the UNION ALL and ORDER BY as bottlenecks.
> 
> From: jwcolby <jwcolby at colbyconsulting.com>
> Date: 2008/04/30 Wed PM 02:59:44 CDT
> To: 
> 	"Discussion concerning Visual Basic and related programming issues."
<dba-vb at databaseadvisors.com>
> Subject: Re: [dba-VB] vb.net - Updates
> 
> I added the nolock in both places and it returned first results in 8 
> seconds instead of 14.  However the whole result set is still taking 
> awhile.  I will post the result when it is in.
> 
> 
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Shamil Salakhetdinov wrote:
>> Yes, it helped, Eric, thanks - the query finished now in ~2 seconds
instead
>> of ~3 seconds....
>>
>> SELECT RowNum, CompetitionresultId from  ( select  
>> 	Cast(ROW_NUMBER() over (ORDER BY [CompetitionresultId]) as int) as
>> RowNum, CompetitionresultId
>>   from [CompetitionresultArc] (nolock)) s  
>> 	where (s.RowNum % 10) = 1 
>> union all
>> Select NULL as RowNum, Max(CompetitionresultId) as CompetitionresultId
from
>> [CompetitionresultArc] (nolock)
>>
>> BTW, Re: JC question on books - I have here "SQL Server 2000, Fast
Answers
>> for DBAs and Developers" by Joseph Sack - ISBN : 1-59059-592-0 - full of
>> useful advices including all kinds of locking - the first time I see so
>> detailed advices and samples on that subject (but I must say I didn't
read
>> that much computer books in the past)...
>>
>> Thanks.
>>
>> --
>> Shamil
>>
>> -----Original Message-----
>> From: dba-vb-bounces at databaseadvisors.com
>> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Eric Barro
>> Sent: Wednesday, April 30, 2008 10:51 PM
>> To: Discussion concerning Visual Basic and related programming issues.
>> Subject: Re: [dba-VB] vb.net - Updates
>>
>> 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
>>
>>
>> _______________________________________________
>> 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
> 
> 
_______________________________________________
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