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