Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Wed Apr 30 16:00:45 CDT 2008
<<< I see the UNION ALL and ORDER BY as bottlenecks. >>> Eric, But union all is executed with two row sets: - first having every 1000th record, and - second having ONLY ONE record... When query execution plan is analyzed then I see that first operation for both parts of UNION ALL query is index scan IOW UNION ALL and ORDER BY do not look as a bottleneck because UNION ALL merges (without any attempts to suppress full duplicate) a set of records with just one record, and because ORDER by of clustered index is 0% cost operation? Thank you -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Eric Barro Sent: Thursday, May 01, 2008 12:25 AM To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [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 _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com