SQLServerCentral / SQL Server 2005 / SQL Server 2005 Performance Tuning

Is there a way to see the cost of a CASE statment

bwilliams-1049831 — Thu, 05 Nov 2009 14:03:14 GMT

The reason I ask is I have a couple of user defined functions. I did not write them!They are both doing 2 CASE statements inside a select statement into a table variable and passing the table variable back to the calling program.This can easly be done in one CASE statement.Normally I would not bother with something this trivial but both functions get exedcuted around 6 million times a week!The essence of the logic is: if language_code = 'french' then use french titleelse use english titleand a second CASE in the same selectif language_code = 'french' concatenate french subtitleelse concantenate english subtitleIt seems a no-brainer that one CASE statement should be used but I would like to know will it make a difference.

Slow execution time on query who perfoms Updates

jocampo — Fri, 06 Nov 2009 07:19:18 GMT

Need help with this store procedure. It sometimes runs ok, others it never finishes. Developer needs to run it manually after waiting for hours. I suspect part of the reason is that is doing lot of inserts on non clustered indexes but I am not sure. Or, some locks on the soon to be updated rows but I have no evidence of that.The two main tables are called col_details and col_eclipse. The 1st table has six indexes, 5 of them are non clustered and are being severely updated during the process.I've attached the MS-SQL plan. By the way, is SQL2008 EnterpriseThanks in advance!PS:For this part of the code, when the INSERT starts to retrieve the soon to be updated rows ... update om set om.System = 'SIQP Indirect', om.Valid = 'Y', om.MCC = e.MCC, om.Note = null from col_detail om, col_eclipse e where om.DealID = e.DealID --new business models added #GG - v2.4 18/02/2008 and e.BusinessModel in ('Volume Channel','Volume Promotion','Value Indirect','Global Indirect') and om.[timestamp] >= @dt_tempI am suggesting thisNon clustered indexes oncol_detail.DealIDcol_eclipse.BusinessModelcol_detail.TimestampWhich implies change TimeStamp column from Clustered to Non clustered index. The other two suggestions need to be created. Like I previously said, not sure about how to improve the other portion of the query plan, the Index Update, Query Plan shows several of them ...

Data allocation in B-tree

Chandhini — Sat, 07 Nov 2009 12:26:55 GMT

Hi,I have 10,000 records in a table. What would be the degree (depth) of the B-tree?.What will determine the depth of the B-tree in a table/database?Thanks in advance.Chandhini

How to improve performance of the view with 12 joins

rshm35 — Fri, 06 Nov 2009 15:22:36 GMT

Hi,I have a view which selects the data with 12 joins in it...can you give me suggestions how can i optimize this view....Thanks

Best Practise

s_shah-977123 — Fri, 06 Nov 2009 15:01:41 GMT

Hi,This has probably been asked several times before, I have been using MS Access previously but now moving to SQL, my server has SQL 2005 workgroup edition installed.- Is it more efficient to use 2 or more databases than or just one?- Is it more efficient to use stored procedures rather than queries from the pages?- Is it more efficient to use one stored proc using a case than using several proc?Thanks for any advice.

Is there any debugging tool like we have in vs.net for executing procedures?

karthimca07 — Thu, 05 Nov 2009 05:08:30 GMT

Is there any debugging tool like we have in vs.net for executing procedures?

How to improve processing of data

Naresh-209545 — Thu, 05 Nov 2009 05:53:58 GMT

Hi,We have 3 tables in our database and details of table is as follows:1) [b]Member[/b] Table contains MemberID, Status, MemberType, OpeningBalance2) [b]Transaction[/b] Table contains MemberID, Year, Month, Day, Value, TransactionType3) [b]TransactionTypes[/b] Table contains TransactionType, Status (Valid / Invalid)Using above tables, we need to generate [b]TransDATA[/b] table based on below creteria1) All valid TransactionType records must be fetch from Transaction Table2) For each member for each transaction type for each day must be copied / created to [b]TransDATA[/b] table.3) If for a particular day for particular transaction type is not found then we need to take it from previous day for same transaction type.Like this we need to generate complete data in a different table.We have member database of 30000, Valid TransactionTypes are maximum 50. When we generate [b]TransDATA[/b] it is taking minimum 23 hours. Tota records in [b]TransDATA[/b] table = [b]30000 x 50 x 30 = 45000000[/b]Can we optimize it? We tried all the way but it is still taking 23 hours :crying: :crying: :crying:.[u][b]06-Nov-2009[/b][/u]I've missed out one more point. If member doesnt have transaction for any day for any TransactionType then we need to create that record in new table.Regards,Naresh Kumar

SQL Server DMV ER Diagram

bugop — Fri, 06 Nov 2009 05:51:52 GMT

Could somebody point me to a source where i can find an ER diagram for SQL Server DMVs. I just want to see how these DMVs relate to each other. How I should join them together. Or how do you get know which DMV to join with which one? I am just wondering. Thanks

Query Slow Performance

sindbad7000 — Thu, 05 Nov 2009 02:13:42 GMT

please i am using sql server 2005, i wrote a simple query that joins only 4 tables. 2 of them have thousands of records. the query returns 4500 records in 1 minute and 30 seconds...is that acceptable??

SQL Server Processor Interupts per second >10 000

cpruden — Thu, 05 Nov 2009 08:34:08 GMT

We have moved to a new 64 bit server with quad processors each with quad cores (16 cores total). I am not noticing performance issues but while monitoring the number of Processor interupts per second I am getting concerned as they appear to be extremely high. >10 000 and at times reaching 40k -50k (total across all). Two cores in paticular appear to be high but we are not pushing the box hard as the %idle time >90%.As we are new to 64bit and quad core systems I am not sure if this is normal. If we have a standby server and it also is high but no where near the maximums of the production system. I understand the concepts of interupts and usually this will be indicative of hardware issues but everything appears to be running normal. A little stumped.Any additional insight would be much appreciated

Memory usage below minimum specified

webrunner — Wed, 04 Nov 2009 19:44:02 GMT

Hello,We have a SQL 2005 64-bit (SP2) SQL Server where the Minimum server memory (in MB) is set to 10240 MB.But in Task Manager, when I just checked, the sqlservr.exe process was using just over 8 GB (8,000,000 KB). Now it's up to about 9,200,000 KB. But that is still below 10240 MB.I thought the minimum setting was a floor below which the memory usage could not drop. Am I wrong?The only thing I can think of is that somehow the sql process was restarted. But I don't see a new log started. Although I do see what looks like a set of errors (I've reversed the order of the log records, but I hope the listing is familiar to someone):1. 11/4/2009 9:21:46 PM ***Unable to get thread context - no pss2. 11/4/2009 9:21:46 PM * BEGIN STACK DUMP:3. 11/4/2009 9:21:46 PM * Non-yielding Schedulerand so on. Does anyone know what might cause this issue? At this link there is mention of possible disk I/O issues, which has me concerned.[url=http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fbf1ad50-b9ae-4388-b03d-08374da85974]http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fbf1ad50-b9ae-4388-b03d-08374da85974[/url]Thanks in advance for any help,webrunner

SQL Server has encountered % occurrence(s) of IO requests taking longer than 15 seconds

Leo.Miller — Thu, 05 Nov 2009 12:35:58 GMT

I've recently had a problem on a SQL2005 SP2 server returning the above message. It turned out the problem was related to the NICs and not SQL. Looking over the history, the NIC performance degraded over about 10 days and so did the general performance, but by the time the IOs were taking long enough to trigger the warning the users were having serious performance issues. This warning is controlled by Trace Flag 830, is there any way to change the default value of 15 seconds to get a warning earlier? I definitely don't want to turn the flag off.Any ideas?Leo MillerSQL Services

Which 3rd Party performance tool do you use?

Indianrock — Wed, 04 Nov 2009 14:15:52 GMT

Which tool do you use and why? Why were others ruled out? Here are the top 3 sql server magazine chose in last year's awards. We currently use spotlight but it's reporting on historical events isn't perfect. Our sister company uses Quest's Performance Analysis for SQL Server (PASS ) so I am considering that even though it wasn't in this top 3. Spotlight requires a diagnostic server and Quest databases on each monitored server, but no agent on the sql server. Quest's PASS does require an agent on each sql server.2008 Gold winner: SQL diagnostic managerIdera • www.idera.comSilver:Spotlight on SQL Server EnterpriseQuest Software • www.quest.comBronze:Embarcadero Performance CenterEmbarcadero • www.embarcadero.com

Database Performance

su_kumar11 — Wed, 04 Nov 2009 05:43:55 GMT

Hi, Can anyone can tell me that how to check database performance in sql server.Thanks,Sunil Kumar

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

junk0 — Thu, 04 Sep 2008 08:42:28 GMT

From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes.The server box specs are: 4 Quad core CPU, 64 GB memory and DB is on a SAN drive - windows 2003 R2 Ent SP2 32 bit, SQL 2005 Ent SP1 32 bit + hotfixes.SQL configured memory is 62 GB.At the peak of the slowdown I checked sys.dm_exec_query_resource_semaphores and it shows high waiter_count and no available_memory, also total_memory_kb is only 44200. Is this normal? Can I somehow increase the available memory for semaphore?Maybe someone knows what might be causing this and how to avoid this problem?

Replay internal error from Profiler

SQL Guy-482125 — Wed, 04 Nov 2009 09:10:30 GMT

Hi,Hi did anybody try to replay a trace in SQL Sever 2005 Profiler ?I created a trace from production server with all required events for replay, but when tried to replay in test server (with restored database from production), for each event class RPC:Starting I got an error "Replay internal error" with text "Unable to execute event, because no matching connection was found on the main MARS thread". What should I do about it ? How can i recreate Multiple active Result Set thread in test ? Is it OK to ignore this error ? It did not put any errors against RPC:Completed event though.

Is this query bad?

Mahesh-300958 — Sun, 01 Nov 2009 20:16:00 GMT

Hi - I had written a data correction script and I was told that this is one of the worst queries written as this would perform badly. I want to know if anyone else had any thoughts / suggestionsUPDATE Orders SET Orders.OrderTypeDescription = CASE WHEN AF.OrderId = 'A' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'X') WHEN AF.OrderId = 'B' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Y') WHEN AF.OrderId = 'C' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'Z') WHEN AF.OrderId = 'D' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'YY') WHEN aF.OrderId = 'E' Then (SELECT OrderTypeId FROM LookUp Where OrderType = 'ZZ')EndFROM Orders AF INNER JOIN with <5> tablesWHERE AND AF.OrderType in ('X','Y','Z','XX','YY','ZZ')

A few IO related DMV queries on my blog

Paul Hayes-294329 — Wed, 04 Nov 2009 07:35:31 GMT

http://sqlmonkey.blogspot.comFeel free to use at will, cheers.

qouestion about Index

farax_x — Mon, 02 Nov 2009 23:30:22 GMT

Hi,For reach best performance, its better to put all columns in one index or create one index per column that must have index ?

Long running query issue...

Barkingdog — Tue, 03 Nov 2009 20:39:05 GMT

One of our developers wrote a query that seems to execute and never complete on one server. (Runs in about 20 minutes on another server). When run, I can not see any locking (sp_lock) or other issues under sp_who2. Profiler shows it starts but after a few hours has not ended. The Database tuning wizard indicates it could improve the query by only 3%.Can you offer greater insight in what to look at next?TIA,barkingdog

Monitoring Index Growth

Jon.Morisi — Tue, 03 Nov 2009 16:32:21 GMT

Anyone have a good way to monitor index growth? My end goal is to trend this growth in order to come up with an appropriate fill factor for index rebuilds.I found this undocumented sp which looks like it would be beneficial to use to gather a metric on growth of indexes[url=http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm]sp_MSindexspace[/url]I then wrote this little script to pull all indexes from a table[code="sql"]EXEC sp_MSforeachtable "exec sp_MSindexspace '?'"[/code]I suppose you could then expand that to run on all db's[code="sql"]CREATE TABLE #INDEXSPACE(IndexID smallint,index_name nvarchar(384),[size] INT, comments nvarchar(128))DECLARE @command VARCHAR(1000) Select @command = 'Use [' + '?' + '] EXEC sp_MSforeachtable ' + '"exec sp_MSindexspace ' + +'''' + '@' + '''' + '"' + ', @replacechar = ' + '''' + '@' + '''' + ''INSERT #INDEXSPACE EXEC sp_MSForEachDB @commandSELECT * FROM #INDEXSPACE ORDER BY index_nameDROP TABLE #INDEXSPACE[/code]This is all a work in progress...I'm actually hoping there's a cleaner way to trend index growth.Thanks ahead of time :-)

query suddenly goes slow - Worktable logical reads high

crichardson-782748 — Mon, 01 Sep 2008 06:58:32 GMT

I just got a support call for a procedure that is running slow in production but goes fast in the test environment. I isolated the statement within the sproc that was the cause of the slow down. I ran the same query in production and test with SET STATISTICS IO ON and the only significant difference is: -Production : Table 'Worktable'. Scan count 1, logical reads 1054329, physical reads 0Test : Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0I tried updating statistics and clearinging the proc cache but didn't make any difference. Any suggestions on a way forward with this?

Ways to Optimize Large numbers of Wild Card Searches

huston.dunlap — Fri, 30 Oct 2009 12:19:48 GMT

Currently I am working on a database table that has a very large text field (we will call it ExtractedText). The query has to look for wildcard searches through the whole text, and determine how many records each term hits on.Currently the basis of the query looks like this:select ID FROM TABLE WHERE ExtractedText like '%BILL%' UNION ALLselect ID FROM TABLE WHERE ExtractedText like '%Tommy%' UNION ALLselect ID FROM TABLE WHERE ExtractedText like '%Willy%'NOTE: Each term has to be run on each record, because I need the number of records each term hits on.We are doing this for over 300 different terms on a large database, so days of running are not uncommon.I am looking for ways to speed this up.I have recently heard of Full Text indexes which may be a solution to the problem, but the results would have to be exactly the same as if I ran the above query. Would that be the case with a Full Text Index search?Are there other techniques I can use to speed this up?

"in" vs. "=" in queries...

Mike Austin-398977 — Mon, 02 Nov 2009 12:24:26 GMT

All,I just started a short contract to help optimize and tune the queries and database for a small company that sells a web service.I've noticed that the developer(s) use the following contruct almost as a standard:where a.ColumnName IN (4)I have conducted a few tests to determine if this is slower thanwhere a.ColumnName = 4The results are inconclusive; sometimes "IN" is faster, sometimes "=" is faster.What do you think?TIA,Mike

Query performace issues and general optimization queries

david.morrison-1035652 — Mon, 02 Nov 2009 06:53:09 GMT

Hi All, I have a few queries around sql server optimization. a couple are quite specific and a couple are not so :-)Firstly, I have a database that I have designed and built as a data store type solution for a number of my companies clients. It has a web front end on it to show the data in a nice format and I create views, on top of my CRUD views, for the application to get this data. This has all been great until the latest project when the quantity of data has hit the 150K - 200K record mark.I've started to experiance performance issues just doing straight select * from a number of the views the application uses.One of my major issues seems to be one of my paticular table structures. I basically have a table that stores name and value pair data, I call it a variables table, but effectivly its like a bunch of virtual fields and thier values assocated with the relevant "flat" record by an ID column. I then Pivot this data into a view which presents a record for each "flat" row with all the virtual columns. See table and index defs below for "variables" table:CREATE TABLE [dbo].[variable_value]( [id] [int] IDENTITY(1,1) NOT NULL, [vrbl_id] [int] NOT NULL, [acct_id] [int] NULL, [cont_id] [int] NULL, [addr_id] [int] NULL, [lead_id] [int] NULL, [last_updated] [smalldatetime] NOT NULL, [date_value] [smalldatetime] NULL, [numeric_value] [int] NULL, [unicode_string_value] [nvarchar](max) NULL, [string_value] [varchar](max) NULL, [switch_value] [bit] NULL, CONSTRAINT [PK_variable_value] PRIMARY KEY NONCLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[variable_value] WITH NOCHECK ADD CONSTRAINT [FK_variable_value_account] FOREIGN KEY([acct_id])REFERENCES [dbo].[account] ([id])GOALTER TABLE [dbo].[variable_value] CHECK CONSTRAINT [FK_variable_value_account]GOALTER TABLE [dbo].[variable_value] WITH NOCHECK ADD CONSTRAINT [FK_variable_value_address] FOREIGN KEY([addr_id])REFERENCES [dbo].[address] ([id])GOALTER TABLE [dbo].[variable_value] CHECK CONSTRAINT [FK_variable_value_address]GOALTER TABLE [dbo].[variable_value] WITH NOCHECK ADD CONSTRAINT [FK_variable_value_contact] FOREIGN KEY([cont_id])REFERENCES [dbo].[contact] ([id])GOALTER TABLE [dbo].[variable_value] CHECK CONSTRAINT [FK_variable_value_contact]GOALTER TABLE [dbo].[variable_value] WITH NOCHECK ADD CONSTRAINT [FK_variable_value_lead] FOREIGN KEY([lead_id])REFERENCES [dbo].[lead] ([id])GOALTER TABLE [dbo].[variable_value] CHECK CONSTRAINT [FK_variable_value_lead]GOALTER TABLE [dbo].[variable_value] WITH NOCHECK ADD CONSTRAINT [FK_variable_value_variable] FOREIGN KEY([vrbl_id])REFERENCES [dbo].[variable] ([id])GOALTER TABLE [dbo].[variable_value] CHECK CONSTRAINT [FK_variable_value_variable]I then hook the pivoted version of this table into a couple of other views and these are the ones taking the time (currently circa 35 seconds for 150k rows). Looking at the execution plan it always does an idex scan of the above table and I can for love nor money figure out how to get it to do index seeks which I assume would speed things up no endSee below the code for the view that includes this pivoted data, views are called vw_contact_variables and also vw_account_variables:CREATE view [ssApp_views].[contacts] asselect co.external_id as [Siebel_Contact_ID], co.id as [contact_id], av.deleted as [Account_Deleted], ac.id as [Account_ID], cv.[Created], cv.[Created_By], co.last_updated as [Updated], cv.[Updated_By], co.title as [Mr_Mrs], co.first_name as [First_Name], co.last_name, ad.line_1 as [Contact_Address_1], ad.line_2 as [Contact_Address_2], ad.town as [Contact_City], ad.postcode as [Contact_Post_Code], ad.country as [Contact_Country], co.email as [email_address], mn.formatted_number as [Mobile_Phone], wn.formatted_number as [Work_Phone], cv.[Intl_Channel_Seg], cv.[Intl_Partner_Flag], cv.[Local_Channel_Seg], cv.[Source], cv.[Certification], cv.[Validated_On], jo.title as [Business_Card_Title], cv.[Comments], cv.[Business_Function], co.call_pref as [call_Permission], cv.[Category], cv.[Category_Value], cv.[Contact_Team], case coalesce(co.email, '') when '' then 0 else 1 end as [email_populated], co.email_pref as [email_Permission], cv.[Inactive_Flag], cv.[Level], co.mail_pref as [Mail_Permission], cv.[Source_Description], cv.[Audience], ad.site_employees, cv.Demand_Generation_Campaign_UID,cv.purl, cv.pin, cv.intimis_contact_idfrom vw_contact as co joinvw_account as ac on ac.id = co.acct_id joinvw_contact_variables as cv on cv.cont_id = co.id left outer joinvw_address as ad on ad.id = co.addr_id left outer joinvw_telephone_full as mn on mn.cont_id = co.id and mn.description = 'Mobile' left outer joinvw_telephone_full as wn on wn.cont_id = co.id and wn.description = 'DDI' joinvw_account_variables as av on av.acct_id = ac.id left outer joinvw_job as jo on jo.id = co.job_idSo basically I need help! I've made some head way into the optimization but I think I need some advice. I need to make these views run quicker. 35 seconds doesn't sound a lot but when I join this view into another "accounts" view it can take up to 5 minutes to run, and thats not acceptable in anybodys book.Also as a side not the query analyser has suggested a some stuff, some of which I've applied and some of which made absolutly no sense to apply.Any help would be greatly welcomed!Thanks :-)Dave

Query parses fine; DTA reports syntax error

Barkingdog — Thu, 03 May 2007 12:22:00 GMT

The query parses fine under Query Editor but generates an error when run under the Database Tuning Advisor. Here's the error message.

"All the events in the workload were ignored due to syntax errors. The most common reason for this error is that the database to connect has not been set correctly."

What does "not set correctly" mean? Any idea what the real problem is?

Bill

P.S. Unfortunately the query is mind-numbingly long or I'd reproduce it here.

Looking for reference materials about general time costs with different actions in SQL

huston.dunlap — Sat, 31 Oct 2009 09:49:31 GMT

So far in my very brief trek into improving optimization, the folks on these boards have been very helpful in improving specific queries.I would like to expand my knowledge on this a lot more. I was wondering if there were any good reference materials out there that go through general performance comparisons between different kinds of actions.For example the efficiency of doing sums to count your items as you do an action vs inserting new numbers into a temp table, and thousands of other possibilities.I recognize that SQL's internal optimizer creates execution paths based on the query you write, but I was wondering if there were some general guidelines out there (beyond not using cursors of course:-))

performance testing SQL server 2005

vaidehi.rajamani19 — Mon, 26 Oct 2009 01:05:39 GMT

Hi,I am new to Performance testing. Rite now we are doing performance testing for our .net application using JMeter tool. Since we are getting high response time in our tool, we are trying to find the performance of our DB also.The biggest problem is we are having multiple DB. The application uses multiple DB to retreive data such as account DB, License DB , etc and then login into the application. Kindly help me out in this regard.

High ASYNC_NETWORK_IO waits

Vincent Central — Fri, 30 Oct 2009 08:33:52 GMT

I have been looking at wait stats to try and figure out some processing time problems that I have not been able to improve with simeple index analysis and improvement, and I see the leading cause of resource waits is ASYNC_NETWORK_IO. It turns out this is not merely pointing at a network bottleneck, as the name implies. Instead, it indicates that the applications hitting the DB server are querying large results sets but not processing all those rows. How can I go about finding which are the problem queries. In other words, how do I know what queries are pulling lots of records but only processing a few, without getting into the application code?Is it also possible this wait type is the result of only processing rows one at a time as with cursors (one of the major apps hitting this server is cursor based)?

impact of stopping a reindexing activity in between

Low Rider — Thu, 29 Oct 2009 23:33:09 GMT

Hisuppose i am reindexing a large table using dbcc dbreindex or alter index statment in sql 2005 ent edition or any sql server for that matter.I for some reason such as large log files , low disk space or locks etc decide to stop the reindexing what impact can it happen on the integrity of the database or the table data on which i was ding the reidexing.will it harm the data in any way espeically if it is a clustered index ?? thanks

When to update index statistics

srimkumarp — Wed, 05 Dec 2007 02:19:07 GMT

hi, recently we moved entry logins and databases from old server 2005 to new server 2005. is it complusory to update statistics .if complusory,When to update index statistics.(down time or peak hours).can any one pls suggest the above one..thanks kumar

CXPACKET too high

sarvesh singh-457805 — Wed, 28 Oct 2009 16:31:11 GMT

Dear friends,I am running the following script to get the wait stats:[code="sql"]WITH Waits AS( SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn, 100. * signal_wait_time_ms / wait_time_ms as signal_pct FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 AND wait_type NOT LIKE N'%SLEEP%' AND wait_type NOT LIKE N'%IDLE%' AND wait_type NOT LIKE N'%QUEUE%' AND wait_type NOT IN( N'CLR_AUTO_EVENT' , N'REQUEST_FOR_DEADLOCK_SEARCH' , N'SQLTRACE_BUFFER_FLUSH' /* filter out additional irrelevant waits */ ))SELECT W1.wait_type, CAST(W1.wait_time_s AS NUMERIC(12, 2)) AS wait_time_s, CAST(W1.pct AS NUMERIC(5, 2)) AS pct, CAST(SUM(W2.pct) AS NUMERIC(5, 2)) AS running_pct, CAST(W1.signal_pct AS NUMERIC(5, 2)) AS signal_pctFROM Waits AS W1 JOIN Waits AS W2 ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct, W1.signal_pctHAVING SUM(W2.pct) - W1.pct < 80 -- percentage threshold OR W1.rn <= 5ORDER BY W1.rn;GO[/code]I get the following result:CXPACKET 1107177.19 95.65 95.65 5.94SOS_SCHEDULER_YIELD 15060.96 1.30 96.95 100.00PAGEIOLATCH_SH 10618.83 0.92 97.86 0.13ASYNC_NETWORK_IO 7497.67 0.65 98.51 0.99WRITELOG 6786.71 0.59 99.10 0.19The server is 16 CPUs. I have changed the maxdop to 12...I have tried different values of cost threshold for parallelis but could not get the PCT down for CXPACKET.;Could you please help?

"There is insufficient system memory to run this query."

Florian Reischl — Wed, 28 Oct 2009 05:36:14 GMT

Hi!We have problems with our SQL Server 2005. We get the error message "There is insufficient system memory to run this query" several times per day. Whenever this happens almost all connected processes die.Since I already searched the forum history, some information for you:[ul][li]Service Pack 3 is installed (current version: 9.00.4035.00)[/li][li]sqlservr is configured for parameter "... -g1024"[/li][li]We do not run any resource governor (it's SQL Server 2005)[/li][/ul]Some information about the environment[b]Box[/b]HP DL 380 G5CPU: 2x Xeon Quad-Core E5440 2.83 GHzRAM: 32GB (PAE enabled)OS: Windows Server 2003 Enterprise x86SQL: SQL Server 2005 Standard x86[b]Databases[/b]14 databases up to 240 GB. The main OLTP database is a old school EAV design (legacy) with 180 GB.Is there any possibility to get the statement/batch which causes this error message?Any suggestions would be helpful!If you need any further information, please let me know.Thanks a bunchFlo

simple select view to retrive 40 lkh records taking 2 min

mssdprasad04 — Mon, 26 Oct 2009 10:27:08 GMT

I have a view with three select statements and unionall , iam trying to pull records from a table which has 40 lkh records it is taking 2 min, i tried with procedure but i couldnt find much difference iam using union all as i need duplicate records also , can any one give me idea/sugestion to retrive records from large tables like this.

Index usage stats

rpatil22 — Tue, 27 Oct 2009 07:55:58 GMT

I am tuning a proc and ran it in tuning advisor.It has recommanded to create 1 indexes and 3 statistics.say index recommanded IX5 - on col1,col2,col6However I checked that table and found that there are already 4 indexesIX1 - on col1,col2,col4IX2 - on col4,col7,col8IX3 - on col8,col3,col4IX4 - on col10,col12,col14How to analysze index usage report?"

Advice of Optimization Techniques for Mass Replace Command on a 1 million+ record DB.

huston.dunlap — Tue, 27 Oct 2009 17:15:18 GMT

I currently have a 1 million+ record DB that I am having to do a large number of string replaces on. I should note that the columns I am working on are static, I don't need to worry about outside updates as I work.Take this example:I have a table with 3 rows.BillyTimmyTommyI want to remove the ending on each one (this is just an example, most of my replaces are more dramatic). So my final table should look like this:BillTimTomCurrently what I am doing is creating a TEMP Table to store the records I want to work on (this part is mainly to ensure I am running everything properly. Then I run 3 update statements to do the replaces. The query looks like this:CREATE TABLE #TEMP (FULLNAME nvarchar (50))--We fill the table using a select statement, not relevant to this discussion.UPDATE #TEMP SET FULLNAME = REPLACE(FULLNAME,'BILLY','BILL')UPDATE #TEMP SET FULLNAME = REPLACE(FULLNAME,'TIMMY','TIM')UPDATE #TEMP SET FULLNAME = REPLACE(FULLNAME,'TOMMY','TIM')In the real DB, I am doing this on a million record DB, and doing 200+ replaces, which are taking a very long time. Are there some optimization techniques I can use to speed this up?

Many ELSE IF's

luan.wp — Wed, 28 Oct 2009 07:36:48 GMT

Hi, i'm with procedure very slow, i know that this is returned cause of ELSE IF.my code is this.. tks.[code="sql"]CREATE PROC PNX_50_LABFILTRO@PASTA INT,@FILTRO INT,@OP INT,@EORG INT=NULLASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @DT DATETIME SET @DT=DATEADD(DAY,-5,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))--COLETAIF @FILTRO=1 AND @PASTA=0 AND @OP=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 ELSE IF @FILTRO=1 AND @PASTA=0 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ORDER BY D.DESCRICAOELSE IF @FILTRO=2 AND @PASTA=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ORDER BY E.DESCRICAOELSE IF @FILTRO=3 AND @PASTA=0 AND @OP=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ORDER BY D.DESCRICAOELSE IF @FILTRO=3 AND @PASTA=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 ELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=0 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORGELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=1 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORGELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=2 SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORGELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)ELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=1 SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1ELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=2 SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1 UNION SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)ELSE IF @FILTRO=6 AND @PASTA=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)-- SEPARAÇÃOELSE IF @PASTA=1 AND @FILTRO=1 AND @OP=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 ELSE IF @PASTA=1 AND @FILTRO=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ELSE IF @PASTA=1 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=0 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORGELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=1 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORGELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=2 SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORGELSE IF @FILTRO=5 AND @PASTA=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORGELSE IF @FILTRO=6 AND @PASTA=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)--EXECUÇÃOELSE IF @PASTA=2 AND @FILTRO=1 AND @OP=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 ELSE IF @PASTA=2 AND @FILTRO=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P left join PACIENTES_PROCEDENCIAS D on P.PRC=D.PRC WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC ELSE IF @PASTA=2 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=0 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORGELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=1 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORGELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=2 SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORGELSE IF @FILTRO=5 AND @PASTA=2 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORGELSE IF @FILTRO=6 AND @PASTA=2 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)-- LIBERAÇÃOELSE IF @PASTA=3 AND @FILTRO=1 AND @OP=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 ELSE IF @PASTA=3 AND @FILTRO=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ELSE IF @PASTA=3 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=0 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORGELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=1 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORGELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=2 SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORGELSE IF @FILTRO=5 AND @PASTA=3 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORGELSE IF @FILTRO=6 AND @PASTA=3 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)-- ENTREGAELSE IF @PASTA=4 AND @FILTRO=1 AND @OP=1 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND l.DCL>=DATEADD(MONTH,-2,GETDATE()) ELSE IF @PASTA=4 AND @FILTRO=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DT_LIB IS NOT NULL AND DT_IPR IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC AND l.DCL>=DATEADD(MONTH,-2,GETDATE()) ELSE IF @PASTA=4 AND @FILTRO=2 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ELSE IF @PASTA=4 AND @FILTRO=3 AND @OP=1 SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D WHERE L.DT_LIB IS NOT NULL AND DT_IPR IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC AND l.DCL>=DATEADD(MONTH,-2,GETDATE()) ELSE IF @PASTA=4 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND l.DCL>=DATEADD(MONTH,-2,GETDATE()) ELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=0 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORGELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=1 SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORGELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=2 SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORGELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=0 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)ELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=1 SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1ELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=2 SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1 UNION SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)ELSE IF @FILTRO=6 AND @PASTA=4 SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)[/code][code="sql"][/code]

Efficiency question

klininger — Wed, 28 Oct 2009 10:05:06 GMT

Below I have written two options to join two tables. It represents the data I am working with: The #descriptions table has entries where sometimes field A is enough to determine the recordtype, sometimes A+B is requred, and sometimes A+B+C is required.Is a more involved join better than a bunch of unions?I'm assuming so, but wanted to be sure. We can assume there are indexes on the tables.Anything I'm missing to make this more efficient?[code="sql"]create table #largedataset ( a int, b int, c int, manyotherfields int identity)insert into #largedataset (a, b, c) values (1, 1, 1)insert into #largedataset (a, b, c) values (1, 1, 2)insert into #largedataset (a, b, c) values (1, 1, 3)insert into #largedataset (a, b, c) values (1, 2, 1)insert into #largedataset (a, b, c) values (1, 2, 2)insert into #largedataset (a, b, c) values (1, 2, 3)insert into #largedataset (a, b, c) values (1, 3, 1)insert into #largedataset (a, b, c) values (1, 3, 2)insert into #largedataset (a, b, c) values (1, 3, 3)insert into #largedataset (a, b, c) values (2, 1, 1)insert into #largedataset (a, b, c) values (2, 1, 2)insert into #largedataset (a, b, c) values (2, 1, 3)insert into #largedataset (a, b, c) values (2, 2, 1)insert into #largedataset (a, b, c) values (2, 2, 2)insert into #largedataset (a, b, c) values (2, 2, 3)insert into #largedataset (a, b, c) values (2, 3, 1)insert into #largedataset (a, b, c) values (2, 3, 2)insert into #largedataset (a, b, c) values (2, 3, 3)insert into #largedataset (a, b, c) values (3, 1, 1)insert into #largedataset (a, b, c) values (3, 1, 2)insert into #largedataset (a, b, c) values (3, 1, 3)insert into #largedataset (a, b, c) values (3, 2, 1)insert into #largedataset (a, b, c) values (3, 2, 2)insert into #largedataset (a, b, c) values (3, 2, 3)insert into #largedataset (a, b, c) values (3, 3, 1)insert into #largedataset (a, b, c) values (3, 3, 2)insert into #largedataset (a, b, c) values (3, 3, 3)create table #descriptions ( a int, b int, c int, Info varchar(100))insert into #descriptions values (1, null, null, 'Defintion for 1')insert into #descriptions values (2, 1, null, 'Defintion for 2, 1')insert into #descriptions values (2, 2, null, 'Defintion for 2, 2')insert into #descriptions values (2, 3, 1, 'Defintion for 2, 3, 1')insert into #descriptions values (2, 3, 2, 'Defintion for 2, 3, 2')insert into #descriptions values (2, 3, 3, 'Defintion for 2, 3, 3')insert into #descriptions values (3, null, null, 'Defintion for 3')-- option 1select ds.*, d.Infofrom #largedataset dsinner join #descriptions don ds.a = d.a and (d.b is null or ds.b = d.b) and (d.c is null or ds.c = d.c)-- option 2select ds.*, d.Infofrom #largedataset dsinner join #descriptions don ds.a = d.awhere d.b is nullunion allselect ds.*, d.Infofrom #largedataset dsinner join #descriptions don ds.a = d.a and ds.b = d.bwhere d.c is nullunion allselect ds.*, d.Infofrom #largedataset dsinner join #descriptions don ds.a = d.a and ds.b = d.b and ds.c = d.c[/code]

Subquery vs join

ta.bu.shi.da.yu — Wed, 28 Oct 2009 06:39:41 GMT

Apologies if this is a silly question, but I was wondering if it was possible to convert a subquery that uses an IN clause to a query that uses a join. The following doesn't really work, as it gives different results:[code="sql"]create table #table1 (int colA, int colB)create table #table2 (int colA)insert into #table1 values (1, 567)insert into #table2 values (1)insert into #table2 values (1)insert into #table2 values (1)insert into #table2 values (1)select t1.colB from #table1 t1 where t1.colA in (select t2.colA from #table2 where t2.colA=t1.colA)select t1.colB from #table1 t1 inner join #table2 t2 on t1.colA=t2.colA[/code]The first query will return 4 results, while the second query returns only one result. Any ideas how you would convert this into a query with a join?

Actual Query Plan - Actual Row Count massively exceeds Est Row Count

allen davidson — Mon, 26 Oct 2009 06:37:21 GMT

Hi, I have a fairly simple 3 table join that (sometimes) runs horribly, it can run in a few seconds but depending on the @params can take 40mins[code="sql"]SELECT @NumOrders = COUNT(DISTINCT p.OrderNo) -- get unique order numbersFROM tblSelections s INNER JOIN tblCustomers c ON s.[user email] = c.emailaddress INNER JOIN tblPurchases p ON c.AccountNo = p.AccountNo AND s.CampaignID = p.AllocatedCampaign8DaysPriorWHERE s.CampaignID IN (55) -- < @@ change this AND p.Orderdte BETWEEN @Start AND @End[/code]Looking at the actual execution plan I see that the Actual Row Count for the Index scan on tblCustomers is massive compared to the Estimated row count (1587737472 vs 719084), the table only has 719084 rows. The table tblSelections (s) has 115967 rows where CampaignID = 55. The plan is not warning of any out of date stats. I'm not that experienced at interpreting execution plans but it just seems wrong, I'm hoping curing this may be a pointer to where the problem is. Does anyone have any thoughts?ThanksAllen