How To Optimize Following Query For Execution Time
Mar 18, 2008
Hell All,
Following query takes 7 minutes to execute while using search criteria as shown below in blue text(ie. IN(2006,2007)
if criteria changes to =2006 as shown in 2),this takes 2minutes
But I want expected output as in query 1) in less time.
How to optimize following query for execution time?
1)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year IN('2006','2007') and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year
2)select sum(PB.CONSN_QTY)Consumption,Count(*),PB.BillPro_Year
from tbtrans_prowaterbill PB
INNER JOIN MIDC_AREA MA
ON PB.Area_cd = MA.Area_cd INNER JOIN MIDC_Division MD ON MA.Div_CD = MD.Division_CD
INNER JOIN MIDC_Circle MC ON MD.Circle_CD = MC.Circle_CD
INNER JOIN TBMST_SubDiv TS ON MA.SubDiv_CD = TS.SubDiv_CD
INNER JOIN MIDC_Zone MZ ON MD.Zone_CD = MZ.Zone_CD
INNER JOIN tbmst_consumer TC ON PB.cons_no = TC.Cons_No
INNER JOIN TBMST_CONSTYPE TCT ON TCT.Cons_Type = TC.Cons_Type
where pb.billpro_year = '2006' and MTR_Size = 15 and TCT.Cons_Type = '1A2'
and MZ.Zone_Name = 'MUMBAI' and MC.Circle_NAME = 'MMR' and MD.Division_Name = 'Dombivli' and TS.SubDiv_DESC = 'THANE DIVISION STAFF'
group by PB.BillPro_Year
Hello,I ran a query that I thought would take an hour, but instead took 14hours to run. The consequence was it bogged down our data warehouseand the overnight build was adversely impacted.Is there a local setting I can set to limit the execution time myquery will take? I dont want to have a server setting and impact otherqueries, just the one I am running.I know there will be people asking about the 14 hour build and what isit doing and so forth. I will address that but I also look to thesesituations as a learning opportunity.Thanks in advance.Rob
I have created 2 tables in a database which are mostly similar, the table1 will execute with more speed (take only less than or equal to 1 sec) but the table2 will take 4 or 5 secs to execute the query,moreover the similar datas was presented in both the tables. the eg:- query that i have executed is select max(c_code) from table1 and select max(c_code) from table2, the first one take less than 1 sec and the second one take more than 4 or 5 secs, also there is a procedure i hve written to update both the tables, and i got the time out error, if the table1 alone is updated using the procedure it is OK but the table2 alone is updated using the procedure the time out error will be shown, pls reply the reason for this problem as early as possible, it will be a grateful if anybody reply for this trouble?
I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?
HI, I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
I am using a stored procedure that is behaving badly - the subtree cost is about 2000 and it takes between 3-4 seconds to run, and sometimes it takes over a minute to run. I have made some optimizations that cause the stored procedure to run in generally under 1 second (at most under 2 seconds), but the subtree cost of it jumps to 4000!! All of this while the server was experiencing similar load (the tests were done within minutes of each other).
I know that the subtree cost is a way to gauge the performance of a query against other queries, but I have typically seen the cost go in the same direction as the execution time (they both go up or the both go down).
How does SQL Server determine the cost (I know that is based on statistics, but I was wondering if anyone had more details)? Is it more important to have a lower subtree cost, or a lower execution time? Am I going to get into trouble later with this high subtree cost?
If I have 6-8 queries running in parallel, Whether having a Single connection Manager (for the same source) for all the Extract performs faster or having Distinct Connection Manager for each of the extract performs faster ?
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
I’m facing performance related problem while running following query on SQL Server 2000.
This query is basically used to find last location of each unit that are passed. Here I am passing data like “‘26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @Units variable. But it takes too much time and I don’t get output. Table is having around 5 Million records.
Query:
SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid FROM tblUnit INNER JOIN tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID WHERE tblUnitHistory.UnitHistoryDate IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @Units + ') GROUP BY tblUnitHistory.UnitID) AND tblUnit.UnitID in (' + @Units + ') ORDER BY tblUnit.UnitID
Table Structure:
UnitHistoryID int Primary Key UnitID int Location varchar(200) Latitude decimal 9 Longitude decimal 9 Speed decimal 5 BatteryVoltage decimal 5 ReasonCode int DistanceFromLastLocation decimal 9 UnitHistoryDate datetime
Indexes:
1. Clustered Index on Column UnitID 2. Non-clustered Index on Column UnitHistoryDate 3. Non-clustered Index on Column UnitHistoryID
Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.
I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better. MyTable Columns: id INT PK ,v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item1 BIT ,item2 BIT ,item3 BIT MyTable2 Columns: v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item INT ,FirstKnownDate DATETIME All columns are NOT NULL.1 DECLARE @iNextRowID INT 2 ,@iCurrentRowID INT 3 ,@iLoopControl INT 4 ,@v1 VARCHAR(4) 5 ,@v2 VARCHAR(10) 6 ,@v3 DECIMAL 7 8 /* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */ 9 SET @iLoopControl=1 10 SELECT @iNextRowID=MIN(id) FROM MyTable WHERE item1=1 11 12 IF ISNULL(@iNextRowID,0) <> 0 BEGIN 13 SELECT @iCurrentRowID = id 14 ,@v1 = v1 15 ,@v2 = v2 16 ,@v3 = v3 17 FROM MyTable 18 WHERE id=@iNextRowID 19 20 WHILE @iLoopControl = 1 BEGIN 21 IF NOT EXISTS(SELECT * FROM MyTable2 WHERE v1=@v2 AND v2=@v2 AND v3=@v3 AND item=1) BEGIN 22 INSERT INTO MyTable2 23 /* COLUMN */ (v1,v2,v3,item,firstknowndate) 24 SELECT @v1, @v2, @v3, 1, GETDATE() 25 END 26 27 SELECT @iNextRowID = NULL 28 SELECT @iNextRowID = MIN(id) FROM MyTable WHERE id>@iCurrentRowID AND item1=1 29 30 IF ISNULL(@iNextRowID,0) = 0 BEGIN 31 BREAK 32 END 33 34 SELECT @iCurrentRowID = id 35 ,@v1 = v1 36 ,@v2 = v2 37 ,@v3 = v3 38 FROM MyTable 39 WHERE id=@iNextRowID 40 END 41 END42 /* This is the end of the items loop */ This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items. MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this? EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?
SELECT s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo FROM saldakonti s WHERE s.datum<'2005-01-01' AND s.uplata='R' AND EXISTIS (SELECT t.temeljnica, t.rbr FROM saldakonti t WHERE t.konto=s.konto AND t.sifra_partnera=s.sifra_partnera AND t.vezni_broj=s.vezni_broj and t.datum<'2005-01-01' AND (t.uplata='S' or t.uplata='U') GROUP BY t.temeljnica, t.rbr HAVING abs(sum(t.duguje-t.potrazuje))<>abs(s.saldo)) ORDER BY s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo
Currently this query takes forever to execute. Any suggestions appreciated. Thanks in advance.
Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?
SELECT TOP 101 Cert_WarehouseDetails.CertID, Cert_WarehouseDetails.UnderwriterID, Cert_WarehouseDetails.WarehouseEntriesID, Cert_WarehouseDetails.DivisionWarehouseLocID, Cert_WarehouseDetails.TypeID, Cert_WarehouseDetails.ReportingType, Cert_WarehouseDetails.CertWHID, Cert_WarehouseDetails.ClientPolicyDivisionID, Cert_WarehouseDetails.RecordNO, Cert_WarehouseDetails.InsuredValueX, Cert_WarehouseDetails.PremiumTotalX, Cert_WarehouseDetails.StatusX, Cert_WarehouseDetails.StatusID, Cert_WarehouseDetails.AuthorizeDateX, Cert_WarehouseDetails.CodeX, Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.BillofLadingDateX, DOGX, Cert_WarehouseDetails.ConveyanceNameX, Cert_WarehouseDetails.LogonNameX , Cert_WarehouseDetails.ClientNameX, (CASE WHEN UnderwriterID = 0 THEN ' ' ELSE tblClientUsers.FirstName + ' ' + tblClientUsers.LastName END) AS OwnedBy, (CASE WHEN UnderwriterID = 0 THEN CAST(' ' AS NVARCHAR) ELSE CAST(TakeOwnershipDate as nvarchar) END) AS OwnedDate
FROM ( SELECT tblCertificates.[CertID] AS CertID, tblCertificates.[UnderwriterID] AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, NULL AS WarehouseEntriesID, NULL AS DivisionWarehouseLocID , tblCertificates.CertID AS CertWHID, tblCertificates.ClientPolicyDivisionsID AS ClientPolicyDivisionID, tblCertificates.CertificateNo AS RecordNO, tblCertificates.TotalInsured AS InsuredValueX, (CASE WHEN tblCertificates.Status=101 or tblCertificates.Status=104 or tblCertificates.DivReferral=1 THEN 0 ELSE PremiumTotal-tblCertificates.Discount END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblCertificates.Status < '105' and tblCertificates.Status <> '103' THEN null ELSE AuthorizeDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblCertificates.IssuedDate AS IssuedDateX, tblCertificates.BillofLadingDate AS BillofLadingDateX, tblCertificates.DescriptionofGoods AS DOGX, tblCertificates.ConveyanceName AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblCertificates.TakeOwnershipDate AS TakeOwnershipDate, tblCertificates.ClientID AS ClientID, tblCertificates.Producer AS BrokerID, tblCertificates.SBU AS SBU, tblCertificates.AssociationID AS AssociationID, tblCertificates.AssuredName AS AssuredName, tblCertificates.UserID AS UserID, tblCertificates.Demoflag AS Demoflag FROM tblCertificates, tblReportingType,tblcurrency,tblClientUsers,tblCli ent,tblStatus WHERE tblCertificates.reportType = tblReportingType.TypeID AND tblCertificates.PremiumCurrencyType = tblCurrency.ID AND tblclientUsers.UserID = tblCertificates.UserID AND tblcertificates.ClientId = tblclient.ClientID AND tblStatus.StatusID = tblCertificates.Status UNION SELECT NULL AS CertID, NULL AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, tblWarehouseEntries.[WarehouseEntriesID] AS WarehouseEntriesID, tblWarehouseEntries.[DivisionWarehouseLocID] AS DivisionWarehouseLocID , tblWarehouseEntries.WarehouseEntriesID AS CertWHID, tblWarehouseEntries.ClientPolicyDivisionID AS ClientPolicyDivisionID, tblWarehouseEntries.WarehouseEntriesID AS RecordNO, (CASE WHEN ValueCurrencyType=0 THEN 0 ELSE UnitValue END) AS InsuredValueX, (CASE WHEN tblWarehouseEntries.StatusID=101 THEN 0 ELSE PremiumSum END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblWarehouseEntries.StatusID < '105' THEN null ELSE ApprovalDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblWarehouseEntries.IssuedDate AS IssuedDateX, tblWarehouseEntries.PeriodEndDate AS BillofLadingDateX, LocName AS DOGX, '' AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblWarehouseEntries.TakeOwnershipDate AS TakeOwnershipDate, tblWarehouseEntries.ClientID AS ClientID, tblWarehouseEntries.BrokerID AS BrokerID, tblWarehouseEntries.SBU AS SBU, tblWarehouseEntries.AssociationID AS AssociationID, tblWarehouseEntries.AssuredName AS AssuredName, tblWarehouseEntries.UserID AS UserID, tblWarehouseEntries.Demoflag AS Demoflag FROM tblWarehouseLoc, tblWarehouseEntries, tblReportingType,tblCurrency ,tblClientUsers,tblClient, tblDivisionWarehouseLoc,tblStatus WHERE tblWarehouseEntries.reportTypeID = tblReportingType.TypeID and tblWarehouseLoc.WarehouseLocationID = tblDivisionWarehouseLoc.WarehouseLocID and tblDivisionWarehouseLoc.DivisionWarehouseLocID = tblWarehouseEntries.DivisionWarehouseLocID and tblWarehouseEntries.PremiumCurrencyType = tblCurrency.ID and tblWarehouseEntries.UserID = tblClientUsers.UserID and tblClient.ClientID = tblWarehouseEntries.ClientID AND tblStatus.StatusID = tblWarehouseEntries.StatusID
) AS Cert_WarehouseDetails LEFT JOIN tblClientUsers ON Cert_WarehouseDetails.UnderwriterID = tblClientUsers.UserID
WHERE 1 = 1 AND Cert_WarehouseDetails.Demoflag = 0 and (convert(datetime,convert(nvarchar,Cert_WarehouseD etails.IssuedDateX,101)) >= '1/1/2003') and (Cert_WarehouseDetails.IssuedDateX is not null and convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004') ORDER BY Cert_WarehouseDetails.RecordNO
In above query,as 1. Union (INNER) query returns me 200000 records.
2. If I run above query it takes 18 sec because no index on Cert_WarehouseDetails.RecordNO and i'm ordering on that.
3. When I avoid to 'ORDER BY Cert_WarehouseDetails.RecordNO' then query takes 2 sec.
4. In this case Do I have to use 'nvarchar' convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')
5. Why do we have to use 'nvarchar' can you explain to me in above statement..
Can you provide me alternate solution so I can make it fast.
Hi,I have these two tables in a DatabaseITEMSIDnumeric (Primary key)ZDIDnvarchar 3 (not null)IDF_Familynumeric(not null)Descriptionnvarchar40 (not null)DATASIDnumeric(Primary Key)IDF_Itemnumeric(Foreign key)IDF_Referencenumeric(Foreign Key)[Date]smalldatetime(not null)Containernchar10(not null)Averagedecimal(not null)[%Compliance]decimal(not null)[%OutOfRange<MinTg]decimal(not null)[%OutOfRange>MaxTg]decimal(not null)Targetdecimal(not null)[Min]decimal(not null)[Max]decimal(not null)The table DATAS has 4000000+ recordsI'm running this query:SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.DescriptionFROM Items as I, Datas as DWHERE D.IDF_Item = I.ID AND I.IDF_Family = 84AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'it's taking 4-5 minutes to run.The result is correct, there is no thing on that date.I've done a reindex, but still the same thing.What can I do?Thanks
Hello,I am trying to find all records that have matching detail_1 or detail_2columns. This is what I have now:select t1.id, t1.detail_1, t1.detail_2, t2.id from user_details t1,user_details t2where t1.detail_1 = t2.detail_1 or t1.detail_2 = t2.detail_2;Using smaller tables of around 1000 records, I have verified that thisindeed does the job. However, when I apply it to the real table thathas around 40,000 records, it takes so long that I have to stop thequery.The table in question is quite simple and is created as follows. Thereare no indexes on the table:create table user_details (id integer, detail_1 varchar(50), detail_2varchar(50)Is there a way to make it go faster?
Hi! I have an optimisation problem. I've currently got a query that retrieve about 10K records according the mode that user difined earlier.
At the very beginning I've wrote the same code through the user-defined functions, but I found in publications that it's a wrong way to develop with MSSQL, but it worked more and more faster (3 sec). Now I rewrite my query with inline table functions...CREATE FUNCTION ans_app_name1()
RETURNS TABLE AS RETURN (SELECT CASE WHEN OBJECT_ID('app_name') > 0 THEN (SELECT TOP(1) app_name FROM app_name WHERE spid = @@spid) ELSE NULL END AS app_name ) GO
CREATE FUNCTION ans_get_user_property1( @property_name VARCHAR(255)) RETURNS TABLE AS RETURN (SELECT u.value AS user_property FROM user_property u JOIN ans_app_name1() a ON a.app_name = u.app_name WHERE property = @property_name AND username = system_user) GO
CREATE FUNCTION ans_get_mode() RETURNS TABLE AS RETURN (SELECT CAST(user_property AS INT) AS mode FROM ans_get_user_property1('mode')) GO
CREATE FUNCTION ans_get_state_date() RETURNS TABLE AS RETURN SELECT CASE WHEN ((SELECT mode FROM ans_get_mode()) IN (3,6)) THEN CAST(user_property AS DATETIME) END AS state_date FROM ans_get_user_property1('state_date') GO
ALTER FUNCTION is_real1( @mode INT, @state_date DATETIME) RETURNS TABLE AS RETURN ( SELECT uid, CASE
WHEN (begin_date = 0) or (end_date = 0) THEN 0
WHEN ((@mode IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
OR ((@mode IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)
OR ((@mode IN (3,6)) AND (begin_date <= @state_date) AND ((end_date IS NULL) OR (end_date > @state_date)))
OR ((@mode = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))) THEN 1 ELSE 0 END AS is_real FROM host ) GO
ALTER view v_host as SELECT h.uid, h.id_histland, h.id_group, h.numporyadok, h.numgroup, h.objidentnum, h.egrokn_reg_num, h.kad_num, h.ansname, h.objname, h.id_objprotstatus, h.id_kategory, h.id_objkind, h.id_histcultvalue, h.uid_objautor, h.createdate, h.id_scientifictype, h.obj_description, h.id_admdistrict, h.cityblock, h.id_settlement, h.id_admdistrict2, h.p_index, h.id_street, h.housenum, h.housenumpart, h.houseliteranum, h.buildingnum, h.corneradress, h.appartmentnum, h.id_urbanblock, h.id_microdistrict, h.kadblock, h.end_date, h.objlocation, h.parent_uid, h.kaddistrict, h.mundistrict, h.is_committed, h.order_uid, h.begin_date, h.cr_date, h.order_uid_end, h.uid_parent_host, h.is_host_object, h.id_funcassign, h.id_immovobjtype, h.hist_data, CASE WHEN h.obj_code is NULL THEN '<object registered>' ELSE h.obj_code END AS obj_code, s.sname AS sname_objprotstatus, k.sname AS sname_kategory, ok.sname AS sname_objkind, p.sname AS name_property, ISNULL(h.ansname + ' ', '') + h.objname as fullname, h.pre_material, h.hist_view_change, h.adr_id, h.street_id, h.cod_mun, h.hist_object_id, h.scientifictype FROM host h LEFT JOIN sl_objprotstatus s ON h.id_objprotstatus = s.id LEFT JOIN sl_kategory k ON h.id_kategory = k.id LEFT JOIN sl_objkind ok ON h.id_objkind = ok.id LEFT JOIN sl_property p ON h.id_property = p.id --JOIN is_real1((SELECT mode FROM ans_get_mode()), (SELECT state_date FROM ans_get_state_date())) r ON r.uid = h.uid WHERE (CASE
WHEN (begin_date = 0) or (end_date = 0) THEN 0
WHEN (((SELECT mode FROM ans_get_mode()) IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
OR (((SELECT mode FROM ans_get_mode()) IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)
OR (((SELECT mode FROM ans_get_mode()) IN (3,6)) AND (begin_date <= (SELECT state_date FROM ans_get_state_date())) AND ((end_date IS NULL) OR (end_date > (SELECT state_date FROM ans_get_state_date()))))
OR (((SELECT mode FROM ans_get_mode()) = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))) THEN 1 ELSE 0 END) = 1 GO
I've currently got a query that takes two tables with the same structure, and spits out the differences in the second table - this is done to help me see what new records have been added in the second table that don't exist in the first. The only catch is that I have to compare two columns in sort of a pseudo-composite-key. Here's the gist of the table structure:
Table A: ID (Autonumber), Firstname, Lastname, OS, PC Manufacturer
Table B (The new one): Firstname, Lastname, OS, PC Manufacturer
The two columns I have to compare are firstname and lastname, as there aren't any other unique identifiers that match between the two tables. Right now, I'm using some really convoluted WHERE statements, and I suspect that it could be done more cleanly. Does anyone have any suggestions?
The result of this query is shown below... The result shows fields such as Bid_Price_Best_Latest and Ask_Price_Best_Latest may or may not have null values. At present, I use a cursor to get the values of these two fields if any of them is null from the table tblPricesSourcesImportHistory for the latest Import_Date on each record. These two fields are referred to as Bid_Price, Ask_Price in tblPricesSourcesImportHistory.
Now I would like to find out how to do this without the use of a cursor, so that I can speed up this query.
Thanks
select fp.Security_ID, fp.Security_Name, fp.Bid_Price_Best_Latest, fp.Bid_Size_Best_Latest, fp.Bid_Source, fp.Ask_Price_Best_Latest, fp.Ask_Size_Best_Latest, fp.Ask_Source from tblTEST fp left join tblSources s on fp.Security_ID = s.Security_ID inner join tblPricesSourcesImportHistory h on h.Source_ID = s.Source_ID where (fp.Bid_Price_Best_Latest is null) or (fp.Ask_Price_Best_Latest is null) group by fp.Security_ID, fp.Security_Name, fp.Bid_Price_Best_Latest, fp.Bid_Size_Best_Latest, fp.Bid_Source, fp.Ask_Price_Best_Latest, fp.Ask_Size_Best_Latest, fp.Ask_Source order by fp.Security_ID
Not sure if it helps but here are the structures of the tables you may need...
CREATE TABLE [dbo].[tblSources]( [Source_ID] [int] IDENTITY(1,1) NOT NULL, [Security_ID] [smallint] NOT NULL, [Source_Parent] [varchar](50) NOT NULL, [Source_Code] [varchar](20) NOT NULL, [Source_Security_Name] [varchar](50) NOT NULL)
I’m trying to optimize the following view so that it runs faster and to trim the code where possible. It runs for 1:57mins. Is that good or it can run much faster than that? Where do l start? It’s a view for a report. Please help l’m running on SQL 2000
CREATE View SalesTest AS SELECT dbo.Loan.loan_No AS [Loan No], dbo.Customer.customer_No AS [Customer No], dbo.Customer.first_Name + ' ' + dbo.Customer.surname AS Customer, dbo.Employer.employer_Name AS [Employer Name], dbo.Loan.store AS [Store No], dbo.Store.store_Name AS Store, dbo.Region.region_Description AS Region, dbo.Financier.financier_Short + dbo.Term.term_Description AS Product, dbo.Loan.date_Issued AS [Transaction Date], dbo.Loan.capital_Amount AS [Capital Amount], dbo.Loan.interest_Amount AS [Interest Amount], dbo.Loan.interim_Interest_Amount AS [Interim Interest Amount], dbo.Loan.interest_Amount + dbo.Loan.interim_Interest_Amount AS [Interest2 Amount], dbo.Loan.insurance_Amount AS [Insurance Amount], dbo.Loan.admin_Fee AS [Admin Fee], dbo.Loan.total_Amount AS [Total Amount], dbo.Loan_Type.loan_Type_Description AS [Loan Type Description], dbo.Loan.user_Changed AS [User], dbo.Loan.first_Payment AS [First Payment], dbo.Loan.monthly_Payment AS [Monthly Payment], dbo.Loan.repayment_Period AS [Repayment Period], dbo.Loan.outstanding_Amount AS [Outstanding Amount], dbo.Loan.last_Payment_Date AS [Last Payment Date], dbo.Status.status_Description AS Status, CONVERT(Char(3), dbo.Loan.loan_No, 1) AS Company, dbo.Customer.physical_Address1 AS Mine1, dbo.Customer.physical_Address2 AS Mine2, dbo.Customer.physical_Address3 AS Mine3, dbo.Loan.maturity_Date AS [Maturity Date], dbo.Agent.agent_Short AS Agent, dbo.Financier.financier_Short AS Financier, dbo.Loan.product AS [Loan Product], dbo.Deduction_Detail.teba_Account_No AS [Teba Account No] FROM dbo.Loan INNER JOIN dbo.Customer ON dbo.Customer.customer_No = dbo.Loan.customer_No INNER JOIN dbo.Status ON dbo.Status.status = dbo.Loan.status INNER JOIN dbo.Store ON dbo.Store.store = dbo.Loan.store INNER JOIN dbo.Product ON dbo.Product.product = dbo.Loan.product INNER JOIN dbo.Product_Type ON dbo.Product_Type.product_Type = dbo.Product.product_Type INNER JOIN dbo.Financier ON dbo.Financier.financier = dbo.Product_Type.financier INNER JOIN dbo.Term ON dbo.Term.term = dbo.Product.term INNER JOIN dbo.Employer ON dbo.Employer.employer = dbo.Customer.employer INNER JOIN dbo.Region ON dbo.Region.region = dbo.Store.region INNER JOIN dbo.Loan_Type ON dbo.Loan_Type.loan_Type = dbo.Product_Type.loan_Type INNER JOIN dbo.Agent ON dbo.Agent.agent = dbo.Product_Type.agent INNER JOIN dbo.Deduction_Detail ON dbo.Loan.customer_No = dbo.Deduction_Detail.customer_No AND dbo.Loan.deduction_No = dbo.Deduction_Detail.deduction_No WHERE (dbo.Loan.outstanding_Amount <> 0)
tblElements.ID is a foreign key to tblOwner.eID @PrevRec = dynamic number of previous records @owner = owner ID
SELECT TOP 2 tblOwner.eID FROM tblOwner LEFT JOIN tblElements ON tblElements.ID = tblOwner.eID WHERE tblOwner.own_ID = @Owner AND tblElements.ID NOT IN (SELECT TOP + @PrevRec + tblOwner.eID FROM tblOwner LEFT JOIN tblElements ON tblElements.ID = tblOwner.eID WHERE tblOwner.own_ID = @Owner) ORDER BY tblOwner.eID ASC
This query is used to display one record per page on a ASP paging script, I select top 2 because, I want to be able to know if I have another page or record to go to and deside if I need to display the "Next" button.
Also It is suppose to select records only assigned to it's ower. It works fairly quick when viewing first few hundred records, then it takes a very long time.
I have indexed ID and eID as clustered indexes also tblOwner could have multiple instances eID.
We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.
My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.
Please help optimize my slow query!
------ Database schema is:
Column Name Data Type Length Allow Nuls
1 msrepl_tran_version uniqueidentifier 16 0
0 [time] GMTtime (int) 4 0
0 msec int 4 1
0 offset GMToffset (smallint) 2 0
0 type nameType (varchar) 15 1
0 host nameType (varchar) 15 1
0 process nameType (varchar) 15 1
0 dbName nameType (varchar) 15 1
0 ptName nameType (varchar) 15 1
0 description descType (varchar) 47 1
0 rtuName nameType (varchar) 15 1
0 groupName nameType (varchar) 15 1
0 message msgType (varchar) 131 0
0 fgInt tinyint 1 1
0 sevInt tinyint 1 1
0 key1 int 4 1
0 key2 int 4 1
0 spooler tinyint 1 1
------- The database view eventView that's used by my queries: SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time] FROM dbo.summary
------- Fast totals query:
select (select count(*) from [event].[dbo].[eventView]) as Events, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 1)as Low, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 2)as Medium, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 3) as High, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 4) as Low1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 6) as Medium1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 8) as High1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 9) as High2
------- Slow daily query:
declare @myDay datetime declare @begDay datetime
declare @myTable table( myDay datetime, Events int, Low int, Medium int, High int, Low1 int, Medium1 int, High1 int, High2 int )
select @myDay = getdate()
select @begDay = convert(datetime, '10/01/2006')
while @begDay <= @myDay begin
insert into @myTable (myDay) values (convert(varchar(20), @begDay, 101))
update @myTable set Events = (select count(*) as Events from [event].[dbo].[eventView] where convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Low = (select count(*) as Low from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 1 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Medium = (select count(*) as Medium from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 2 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High = (select count(*) as High from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 3 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Low1 = (select count(*) as Low1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 4 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Medium1 = (select count(*) as Medium1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 6 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High1 = (select count(*) as High1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 8 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High2 = (select count(*) as High2 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 9 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
I added the subquery and now this thing consistently takes more than five minutes to return 7100+ rows. Any suggestions? Thanks again, you guys are the best.
ddave ---------------------------- SET @StrQry1 = '(SELECT 1 AS Counter, Q1.SubsidyLevel, VEL.* FROM dbo.ViewEligibilityPHC VEL LEFT OUTER JOIN (SELECT * FROM dbo.MEMB_LISHISTS l WHERE l.LISThruDate is null AND l.Deleted = ''0'') AS Q1 ON VEL.MEMBID = Q1.MemberID WHERE VEL.OPTHRUDT is null AND VEL.OPT LIKE ''' + @HPlan + ''' AND (VEL.PCP IS NULL OR VEL.PCP LIKE ''' + @Prvdr + ''') AND VEL.HCC LIKE ''' + @HCC + ''' AND (VEL.CaseMgrID IS NULL OR VEL.CaseMgrID LIKE ''' + @CaseMngr + ''') AND VEL.OPFROMDT <= CAST(''' + @SDate + ''' AS datetime) AND ISNULL(VEL.OPTHRUDT, CAST(''' + @TDate + ''' AS datetime)) >= CAST(''' + @EDate + ''' AS datetime)) '
SQL 2K Table loaded using BULK INSERT with ~5 000 000 records. CREATE TABLE [MyTable] ( [SeqNumber] int NOT NULL ,-- sequence unique number [ObjId] int NOT NULL , [HierarchyLevel] varchar(255) NULL ,-- highest level = 0 [NameText] varchar(255) NULL . . )
insert into MyTable select 1, 1, 0, 'text-10' union all select 2, 1, 1, 'text-11 state-1' union all select 3, 1, 2, 'text-12 social-1' union all select 4, 1, 3, 'text-13 abc social-1.1' union all select 5, 1, 1, 'text-11 123' union all select 6, 1, 1, 'text-11 ABCDEF' union all select 7, 1, 2, 'text-12 bbb' union all select 8, 1, 3, 'text-13 social-2' union all select 9, 1, 4, 'text-14 aaa' union all select 10, 2, 1, 'text-21 state-3' union all select 11, 3, 5, 'text-31 state-2 social-3'
- Unique Index on SeqNumber
Query: Select top 100 TI.* from [dbo].[MyTable] TI inner join [dbo].[MyTable] TL on TL.ObjId = TI.ObjId where (TL.[NameText] like '%state%') and TI.[NameText] like '%social%' and TI.HierarchyLevel > TL.HierarchyLevel and TI.SeqNumber > TL.SeqNumber and not exists (select * from [dbo].[MyTable] T3 where T3.ObjId = TL.ObjId and T3.SeqNumber > TL.SeqNumber and T3.SeqNumber < TI.SeqNumber and T3.HierarchyLevel <= TL.HierarchyLevel) Union Select T4.* from [dbo].[MyTable] T4 where T4.[NameText] like '%state%' and T4.[NameText] like '%social%' order by Ti.SeqNumber
