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