[dba-VB] vb.net - Updates

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