Performance in big UNION

Before migrating an application to SQL2005, I am checking the performance of
it and comparing it with the performance in SQL 2000. I have found a big
query that has 1000 selects of constant values joined by union all; in the
first execution, it takes more than 20 seconds to run but in sql 2000 it
takes less than 3 seconds, but when the plan is in cache the executions that
follow run in 3 seconds.
You can reproduce it with
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
union all
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
and repeating it till 1000 unions all.
Does anybody know what 2005 is doing in the compilation? and, of course, if
there is any workaround to avoid splitting the query into a subset of queries.
Thanks in advance.Ariel wrote:
> Before migrating an application to SQL2005, I am checking the performance of
> it and comparing it with the performance in SQL 2000. I have found a big
> query that has 1000 selects of constant values joined by union all; in the
> first execution, it takes more than 20 seconds to run but in sql 2000 it
> takes less than 3 seconds, but when the plan is in cache the executions that
> follow run in 3 seconds.
> You can reproduce it with
> select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> union all
> select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> and repeating it till 1000 unions all.
Why are you comparing the performance of such a hm... exotic query?
Do you use it in your application?
First execution of your query takes several seconds, because parse and
compile step takes a lot of time. Nothing can be done about it. Query
processor has been totally rebuilt in SQL Server 2005, you're
experiencing a side effect of these changes.
> Does anybody know what 2005 is doing in the compilation? and, of course, if
> there is any workaround to avoid splitting the query into a subset of queries.
Why do you need this query?
Best regards,
Marcin Guzowski
http://guzowski.info|||I assume that the values within this 1000 line select are static.
Why not load them into a table and select from the table?
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Marcin A. Guzowski" wrote:
> Ariel wrote:
> > Before migrating an application to SQL2005, I am checking the performance of
> > it and comparing it with the performance in SQL 2000. I have found a big
> > query that has 1000 selects of constant values joined by union all; in the
> > first execution, it takes more than 20 seconds to run but in sql 2000 it
> > takes less than 3 seconds, but when the plan is in cache the executions that
> > follow run in 3 seconds.
> >
> > You can reproduce it with
> >
> > select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> > union all
> > select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> >
> > and repeating it till 1000 unions all.
>
> Why are you comparing the performance of such a hm... exotic query?
> Do you use it in your application?
> First execution of your query takes several seconds, because parse and
> compile step takes a lot of time. Nothing can be done about it. Query
> processor has been totally rebuilt in SQL Server 2005, you're
> experiencing a side effect of these changes.
>
> > Does anybody know what 2005 is doing in the compilation? and, of course, if
> > there is any workaround to avoid splitting the query into a subset of queries.
> Why do you need this query?
>
> --
> Best regards,
> Marcin Guzowski
> http://guzowski.info
>|||The query written in this post is only to reproduce the problem, the legacy
code of my application is using a similar code to insert data from a grid to
a table. But with the query of the example you can reproduce the problem.
Isn't there a way to make the optimizer run faster?
Six times slower than in 2000 seems to be too much to be a side effect due
to changes in 2005 , isn't it?
"mrdenny" wrote:
> I assume that the values within this 1000 line select are static.
> Why not load them into a table and select from the table?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
>
> "Marcin A. Guzowski" wrote:
> > Ariel wrote:
> > > Before migrating an application to SQL2005, I am checking the performance of
> > > it and comparing it with the performance in SQL 2000. I have found a big
> > > query that has 1000 selects of constant values joined by union all; in the
> > > first execution, it takes more than 20 seconds to run but in sql 2000 it
> > > takes less than 3 seconds, but when the plan is in cache the executions that
> > > follow run in 3 seconds.
> > >
> > > You can reproduce it with
> > >
> > > select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> > > union all
> > > select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> > >
> > > and repeating it till 1000 unions all.
> >
> >
> > Why are you comparing the performance of such a hm... exotic query?
> > Do you use it in your application?
> >
> > First execution of your query takes several seconds, because parse and
> > compile step takes a lot of time. Nothing can be done about it. Query
> > processor has been totally rebuilt in SQL Server 2005, you're
> > experiencing a side effect of these changes.
> >
> >
> > > Does anybody know what 2005 is doing in the compilation? and, of course, if
> > > there is any workaround to avoid splitting the query into a subset of queries.
> >
> > Why do you need this query?
> >
> >
> > --
> > Best regards,
> > Marcin Guzowski
> > http://guzowski.info
> >|||Ariel wrote:
> The query written in this post is only to reproduce the problem, the legacy
> code of my application is using a similar code to insert data from a grid to
> a table. But with the query of the example you can reproduce the problem.
> Isn't there a way to make the optimizer run faster?
Of course there is a way - faster server.
> Six times slower than in 2000 seems to be too much to be a side effect due
> to changes in 2005 , isn't it?
No, it's not too much.
Use the right method for your task and performance won't be a bottleneck.
I would choose one of two ways to insert data from app to a table.
First (classic, many inserts in one batch, all in one transaction):
BEGIN TRAN
INSERT INTO table (cols..) VALUES (.....)
INSERT INTO table (cols..) VALUES (.....)
INSERT INTO table (cols..) VALUES (.....)
...
COMMIT TRAN
Second (dynamic SQL - very fast):
INSERT table
EXEC
('
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
');
Best regards,
Marcin Guzowski
http://guzowski.info|||Since it is sql 2005, you can actually force the plan. See BOL: Plan
Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint
Not sure if that will help in this scenario.
You mention a grid as the actual source of the problem and this fabricated
massive union all as just a demo. Can you post what the grid is doing, or
fix the ROOT cause there yourself'
TheSQLGuru
President
Indicium Resources, Inc.
"Ariel" <Ariel@.discussions.microsoft.com> wrote in message
news:9E2E9715-4C9E-449F-9A9E-06DD932AFD53@.microsoft.com...
> The query written in this post is only to reproduce the problem, the
> legacy
> code of my application is using a similar code to insert data from a grid
> to
> a table. But with the query of the example you can reproduce the problem.
> Isn't there a way to make the optimizer run faster?
> Six times slower than in 2000 seems to be too much to be a side effect due
> to changes in 2005 , isn't it?
>
> "mrdenny" wrote:
>> I assume that the values within this 1000 line select are static.
>> Why not load them into a table and select from the table?
>> --
>> Denny
>> MCSA (2003) / MCDBA (SQL 2000)
>> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
>> /
>> Microsoft Office SharePoint Server 2007: Configuration)
>> MCITP (dbadmin, dbdev)
>>
>> "Marcin A. Guzowski" wrote:
>> > Ariel wrote:
>> > > Before migrating an application to SQL2005, I am checking the
>> > > performance of
>> > > it and comparing it with the performance in SQL 2000. I have found a
>> > > big
>> > > query that has 1000 selects of constant values joined by union all;
>> > > in the
>> > > first execution, it takes more than 20 seconds to run but in sql 2000
>> > > it
>> > > takes less than 3 seconds, but when the plan is in cache the
>> > > executions that
>> > > follow run in 3 seconds.
>> > >
>> > > You can reproduce it with
>> > >
>> > > select
>> > > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
>> > > union all
>> > > select
>> > > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
>> > >
>> > > and repeating it till 1000 unions all.
>> >
>> >
>> > Why are you comparing the performance of such a hm... exotic query?
>> > Do you use it in your application?
>> >
>> > First execution of your query takes several seconds, because parse and
>> > compile step takes a lot of time. Nothing can be done about it. Query
>> > processor has been totally rebuilt in SQL Server 2005, you're
>> > experiencing a side effect of these changes.
>> >
>> >
>> > > Does anybody know what 2005 is doing in the compilation? and, of
>> > > course, if
>> > > there is any workaround to avoid splitting the query into a subset of
>> > > queries.
>> >
>> > Why do you need this query?
>> >
>> >
>> > --
>> > Best regards,
>> > Marcin Guzowski
>> > http://guzowski.info
>> >|||Thanks for your ideas, I just was trying to avoid changing code and learn
what the optimizer is doing during those 20 seconds. I'll use your ideas to
change the code. I have tried to split the query in other with 'only' 15
UNIONS and the results are good enough for the app, although they continue
being worse than in 2000.
Do you know any information source where I can find information about this
kind of 'surprises' with the query processor'
"TheSQLGuru" wrote:
> Since it is sql 2005, you can actually force the plan. See BOL: Plan
> Forcing Scenario: Create a Plan Guide That Uses a USE PLAN Query Hint
> Not sure if that will help in this scenario.
> You mention a grid as the actual source of the problem and this fabricated
> massive union all as just a demo. Can you post what the grid is doing, or
> fix the ROOT cause there yourself'
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ariel" <Ariel@.discussions.microsoft.com> wrote in message
> news:9E2E9715-4C9E-449F-9A9E-06DD932AFD53@.microsoft.com...
> > The query written in this post is only to reproduce the problem, the
> > legacy
> > code of my application is using a similar code to insert data from a grid
> > to
> > a table. But with the query of the example you can reproduce the problem.
> >
> > Isn't there a way to make the optimizer run faster?
> >
> > Six times slower than in 2000 seems to be too much to be a side effect due
> > to changes in 2005 , isn't it?
> >
> >
> >
> > "mrdenny" wrote:
> >
> >> I assume that the values within this 1000 line select are static.
> >>
> >> Why not load them into a table and select from the table?
> >> --
> >> Denny
> >> MCSA (2003) / MCDBA (SQL 2000)
> >> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration
> >> /
> >> Microsoft Office SharePoint Server 2007: Configuration)
> >> MCITP (dbadmin, dbdev)
> >>
> >>
> >> "Marcin A. Guzowski" wrote:
> >>
> >> > Ariel wrote:
> >> > > Before migrating an application to SQL2005, I am checking the
> >> > > performance of
> >> > > it and comparing it with the performance in SQL 2000. I have found a
> >> > > big
> >> > > query that has 1000 selects of constant values joined by union all;
> >> > > in the
> >> > > first execution, it takes more than 20 seconds to run but in sql 2000
> >> > > it
> >> > > takes less than 3 seconds, but when the plan is in cache the
> >> > > executions that
> >> > > follow run in 3 seconds.
> >> > >
> >> > > You can reproduce it with
> >> > >
> >> > > select
> >> > > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> >> > > union all
> >> > > select
> >> > > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
> >> > >
> >> > > and repeating it till 1000 unions all.
> >> >
> >> >
> >> > Why are you comparing the performance of such a hm... exotic query?
> >> > Do you use it in your application?
> >> >
> >> > First execution of your query takes several seconds, because parse and
> >> > compile step takes a lot of time. Nothing can be done about it. Query
> >> > processor has been totally rebuilt in SQL Server 2005, you're
> >> > experiencing a side effect of these changes.
> >> >
> >> >
> >> > > Does anybody know what 2005 is doing in the compilation? and, of
> >> > > course, if
> >> > > there is any workaround to avoid splitting the query into a subset of
> >> > > queries.
> >> >
> >> > Why do you need this query?
> >> >
> >> >
> >> > --
> >> > Best regards,
> >> > Marcin Guzowski
> >> > http://guzowski.info
> >> >
>
>sql