The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.
But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Sunday, February 26, 2012
Extremely Poor Query Performance - Identical DBs Different Performance
Hello Everyone,
I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.
I encounted various performance issues with the production server with a
particular query. It would take approximately 22 seconds to return 100
rows, thats about 0.22 seconds per row. Note: I ran the query in single
user mode. So I tested the query on the Development server by taking a
backup (.dmp) of the database and moving it onto the dev server. I ran
the same query and found that it ran in less than a second.
I took a look at the query execution plan and I found that they we're
the exact same in both cases.
Then I took a look at the various index's, and again I found no
differences in the table indices.
If both databases are identical, I'm assumeing that the issue is related
to some external hardware issue like: disk space, memory etc. Or could
it be OS software related issues, like service packs, SQL Server
configuations etc.
Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1
I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system related issue.
Any Ideas would help me greatly!
Thanks,
Brian T
*** Sent via Developersdex http://www.developersdex.com ***Brian
Before looking at hardware try running
UPDATE STATISTICS tablename for relevant tables with indexes
and see if it makes a difference to performance
Brian Tabios wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with a
> particular query. It would take approximately 22 seconds to return 100
> rows, thats about 0.22 seconds per row. Note: I ran the query in single
> user mode. So I tested the query on the Development server by taking a
> backup (.dmp) of the database and moving it onto the dev server. I ran
> the same query and found that it ran in less than a second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is related
> to some external hardware issue like: disk space, memory etc. Or could
> it be OS software related issues, like service packs, SQL Server
> configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
> *** Sent via Developersdex http://www.developersdex.com ***|||I've seen this before and acutally it's quite common.
The key to your problem is different execution plans.
A SP can have several copies of a execution plan.
a) Different SET statements to the connection
b) You don't call the SP from QA with proper owner prefix (e.g. dbo)
c) In a multitple CPU environment you will have one scheduler (UMS) for
each SPID, and you might experience that you get the same exection plan
until your thread is closed.
Make sure that you update the statistics whenever you experience such
problems. The stats are stored in server, not in the databases.
So what do you do?
If a procedure gets slow, you can recompile the procedure with
sp_recompile. If this doesn't help, use DBCC FREEPROCCACHE and run the
procedure agin.
SP's are often recompiled in an OLTP environment. Common reason is
change in statistics. Sometimes the optimizer makes a poor choice in
execution plan due to variance in the parameteres it recevies when
recompiling. It's smart to look into what parameters are sent to the
procedure and see if there are great changes. Also pay attention to
complex procedures with if-else and case-statements. Keep it simple!
Regards,
Henrik
*** Sent via Developersdex http://www.developersdex.com ***
Friday, February 24, 2012
extremely bad performance running on huge box ?
hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
extremely bad performance running on huge box ?
hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.are there 4 physcial processors or is this a Xeon box with
Hyper-Threading,
if HT is enabled, then regardless of the number of actual
processors, try OPTION (MAXDOP 1) or 2
suppose your average row size is 100Bytes, then 20M rows
means the table is 2GB in size
a select count(*) requires a full table scan.
if your data is on a single disk, you can probably achieve
a sequential transfer rate of 50MB/sec, meaning your query
should take 40sec,
if you have 2 disks striped, you ought to be closer to
100MB/sec or 20sec.
if the entire table is in memory, the scan rate should be
800MB/sec,
so something is not right.
on the select *, i need to know the index and the full
query,
also keep in mind the time it QA to receive a large number
of rows
>--Original Message--
>hi all !!!
>I have a SQL Server 2000 running on a 4 processor 2 GB
ram DELL box running
>Windows 2003 Server. no other apps run in this machine.
>a table witn 20 million records is truncated and
recreated once every week.
>records are quite simple: (key,value). Table in indexes
on key field.
>the problem is thar running a "select count(*) from
table" takes more than 2
>minutes.
>even worst, doing a "select * from table where (condition
on indexed key
>field)" also takes more than 2 minutes.
>during these queries, which I do from the Query Analyzer,
the overall CPUs
>usage is less than 3%.
>what could be wrong ? any help would be greatly
appreciated.
>thanks in advance,
> ant.
>
>.
>|||Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.are there 4 physcial processors or is this a Xeon box with
Hyper-Threading,
if HT is enabled, then regardless of the number of actual
processors, try OPTION (MAXDOP 1) or 2
suppose your average row size is 100Bytes, then 20M rows
means the table is 2GB in size
a select count(*) requires a full table scan.
if your data is on a single disk, you can probably achieve
a sequential transfer rate of 50MB/sec, meaning your query
should take 40sec,
if you have 2 disks striped, you ought to be closer to
100MB/sec or 20sec.
if the entire table is in memory, the scan rate should be
800MB/sec,
so something is not right.
on the select *, i need to know the index and the full
query,
also keep in mind the time it QA to receive a large number
of rows
>--Original Message--
>hi all !!!
>I have a SQL Server 2000 running on a 4 processor 2 GB
ram DELL box running
>Windows 2003 Server. no other apps run in this machine.
>a table witn 20 million records is truncated and
recreated once every week.
>records are quite simple: (key,value). Table in indexes
on key field.
>the problem is thar running a "select count(*) from
table" takes more than 2
>minutes.
>even worst, doing a "select * from table where (condition
on indexed key
>field)" also takes more than 2 minutes.
>during these queries, which I do from the Query Analyzer,
the overall CPUs
>usage is less than 3%.
>what could be wrong ? any help would be greatly
appreciated.
>thanks in advance,
> ant.
>
>.
>|||Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
Extreme performance issues (SQL Server 2000/ADO.NET/C#)
I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon Rista
Have you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>
|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's[vbcol=seagreen]
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
suggested I post[vbcol=seagreen]
perform a process on[vbcol=seagreen]
through Query
>
>.
>
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon Rista
Have you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>
|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's[vbcol=seagreen]
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
suggested I post[vbcol=seagreen]
perform a process on[vbcol=seagreen]
through Query
>
>.
>
Extreme performance issues (SQL Server 2000/ADO.NET/C#)
I posted this on the ADO.NET group, and someone there suggested I post
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon RistaHave you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
>> I posted this on the ADO.NET group, and someone there
suggested I post
>> it here too. I appreciate any tips.
>> I'm using ADO.NET in a windows service application to
perform a process on
>> SQL Server 2000. This process runs very quickly if run
through Query
>
>.
>
it here too. I appreciate any tips.
I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.
The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.
The process is as follows:
1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.
When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.
When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.
I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.
I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.
I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.
-- Jon RistaHave you profiled both SQL Server and your application to find out where all
of the time is coming from? You should run SQL Server Profiler to find out
if the stored procedures are really taking 30 minutes (and what parameters
are really getting passed in -- it could be that something is not getting
properly passed in). If it turns out that the stored procedures are not
taking up the time, you should download a copy of Compuware's DevPartner
Profiler, Community Edition ( www.compuware.com ). Then you can profile
your app to find out what's going on.
When you say you're "using ADO.NET", what does that mean? Are you using a
data reader? Data adaptor? What are you doing with the data once it's
loaded into the reader/data set?
"Jon Rista" <jrista@.hotmail.com> wrote in message
news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
> I posted this on the ADO.NET group, and someone there suggested I post
> it here too. I appreciate any tips.
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
>|||When you profile your server, if you find that there is a
performance issue with a stored proc, you might want to
include the "Stored Procedure- SP:StmtCompleted" event in
order to see if there is a particular statement within
your stored procs that is takign all the time.
Also, if it isn't already there, add "SET NOCOUNT ON" at
the top of your stored procedures.
We have seen some preformance issue from .NET Web
Services but have not used .NET Windows Services. I have
a feeling that this may actually be your problem.
I hope that this helps somehow.
Matthew Bando
Matthew.Bando@.Remove csctgi.com
>--Original Message--
>Have you profiled both SQL Server and your application
to find out where all
>of the time is coming from? You should run SQL Server
Profiler to find out
>if the stored procedures are really taking 30 minutes
(and what parameters
>are really getting passed in -- it could be that
something is not getting
>properly passed in). If it turns out that the stored
procedures are not
>taking up the time, you should download a copy of
Compuware's DevPartner
>Profiler, Community Edition ( www.compuware.com ). Then
you can profile
>your app to find out what's going on.
>When you say you're "using ADO.NET", what does that
mean? Are you using a
>data reader? Data adaptor? What are you doing with the
data once it's
>loaded into the reader/data set?
>
>"Jon Rista" <jrista@.hotmail.com> wrote in message
>news:e9eGyMsmEHA.2864@.tk2msftngp13.phx.gbl...
>> I posted this on the ADO.NET group, and someone there
suggested I post
>> it here too. I appreciate any tips.
>> I'm using ADO.NET in a windows service application to
perform a process on
>> SQL Server 2000. This process runs very quickly if run
through Query
>
>.
>
Extreamly slow performance on a view
Can anyone see anything that may be causing this view to return data
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>
Subscribe to:
Posts (Atom)