Performance Problem with UNION Query

Hi ,
I must say firstly, my design is a little stupid but It has to like that,
So , I have a table named XX with 98 columns and 230.000 records , it is old
data source and I can't cut into pieces it.
and I have already new datas with new design , I did new view named YY
similiar with XX, and I wanna to merge two structures,
my union query is like that
SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
UNION
SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
this query works slowly for me , how can I make more effiency that
structure?
sorry If I couldn't explain very well.
Thanks for helps
Best Regards
Serkan KARAAssuming the design is not open for discussion:
First step is to determine whether you need to remove duplicated after the U
NION is performed. If
no, change to UNION ALL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"scorpion" <ss@.ss.com> wrote in message news:%23OI3I0UBFHA.2580@.TK2MSFTNGP10.phx.gbl...[col
or=darkred]
> Hi ,
> I must say firstly, my design is a little stupid but It has to like that,
> So , I have a table named XX with 98 columns and 230.000 records , it is o
ld
> data source and I can't cut into pieces it.
> and I have already new datas with new design , I did new view named YY
> similiar with XX, and I wanna to merge two structures,
> my union query is like that
> SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
> UNION
> SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
> this query works slowly for me , how can I make more effiency that
> structure?
> sorry If I couldn't explain very well.
> Thanks for helps
> Best Regards
> Serkan KARA
>[/color]|||Change UNION to UNION ALL.
Run the query and check the plan. Look to see if you have indexes. How
many rows to you expect to return, lots, or very few? Can you index the
view (check in books online, or just try.) Is this going to be executed a
lot? And by slow, do you mean oppressively slow, or just kind of slow.
First step though is to check the plan and look for major trouble spots.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"scorpion" <ss@.ss.com> wrote in message
news:%23OI3I0UBFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I must say firstly, my design is a little stupid but It has to like that,
> So , I have a table named XX with 98 columns and 230.000 records , it is
> old
> data source and I can't cut into pieces it.
> and I have already new datas with new design , I did new view named YY
> similiar with XX, and I wanna to merge two structures,
> my union query is like that
> SELECT column1, column2, .... column98 FROM YY WHERE column5='aaaaaaaa'
> UNION
> SELECT column1, column2, .... column98 FROM XX WHERE column5='aaaaaaaa'
> this query works slowly for me , how can I make more effiency that
> structure?
> sorry If I couldn't explain very well.
> Thanks for helps
> Best Regards
> Serkan KARA
>

Performance of named instance

Hi,
I have an app that ran just fine with the default instance of MSDE. I
changed the connection string in the app to work with a named instance
instead, like this: (local)/MyNamedInstance.
Database performance slowed measurably as soon as I did that, particularly
with DDL commands, like:
CREATE DEFAULT BOOLEAN_DEFAULT AS ''F''
CREATE RULE BOOLEAN_RULE AS @.list IN (''T'', ''F'')
sp_addtype DOM_BOOLEAN, ''VARCHAR(1)'', ''NOT NULL''
sp_bindefault BOOLEAN_DEFAULT, DOM_BOOLEAN
sp_bindrule BOOLEAN_RULE, DOM_BOOLEAN
It literally takes 10-15 seconds to execute about 4 of these lines of code,
where they used to execute pretty much immediately.
Is there anything special I need to do with a named instance of MSDE to
obtain the same performance levels?
Thanks in advance.
-Eric Harmon
If the instances are on the same machine it is possible that the first
instance has taken the majority of the memory and is still using it. If the
first instance needs the memory it will not free it up for the second
instance.
I have never seen a case where the cause of a performance problem was a
default vs. a named instance, so you need to concentrate on the other
differences; resources available, differences in data, schema differences,
disk speed, etc.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob