[dba-VB] vb.net - Updates

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