How To Optimize This Slow Query?
Mar 25, 2004
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.
Is there an easier way of writing this out.
View 3 Replies
ADVERTISEMENT
Feb 2, 2007
Hello friends,
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.
Regards,
Sandeep
View 1 Replies
View Related
Oct 12, 2007
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?
View 4 Replies
View Related
May 25, 2005
Hi all,
I need help to optimize this query:
Code:
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.
View 2 Replies
View Related
Feb 26, 2004
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?
Thanks!
Set rs=Server.CreateObject("ADODB.Recordset")
sql = "SELECT td.TeamID, td.TeamName, rt.PartID, rt.Effort, rt.UnitMeas, pd.MinMilesConv "
sql = sql & "FROM TeamData td INNER JOIN PartData pd ON td.TeamID = pd.TeamID "
sql = sql & "JOIN RunTrng rt ON pd.PartID = rt.PartID "
sql = sql & "WHERE rt.TrngDate >= '" & Session("beg_date") & "' AND rt.TrngDate < '" & Session("end_date")
sql = sql & "' AND pd.Archive = 'N' AND pd.Gender = '" & sGender & "' AND pd.Grade >= " & iMinGrade
sql = sql & " AND pd.Grade <= " & iMaxGrade & " ORDER BY td.TeamID"
rs.Open sql, conn, 1, 2
View 2 Replies
View Related
Mar 25, 2004
Hi all,
Table 'A' is having 105 fields & 233000 records.
One Clusterd Primary Key & 10 nonclusterd key.
If I joined with table 'A' or 'Select * from 'A' , Query takes more time so please let me know best way to structure query or table, Indexes etc.
Reply to me asap positivaly ....
Regards,
M. G.
View 1 Replies
View Related
Apr 1, 2004
Hi All,
please look into below query.
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.
Please reply to me asap. Thanks in advance.
Regards,
M. Jain
View 10 Replies
View Related
May 18, 2006
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
View 4 Replies
View Related
Nov 17, 2006
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?
View 4 Replies
View Related
Mar 3, 2008
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
set statistics time on
-- cleaning cache
dbcc dropcleanbuffers
dbcc freeproccache
SELECT * FROM v_host
/*
(9777 row(s) affected)
SQL Server Execution Times:
CPU time = 9718 ms, elapsed time = 10834 ms.
*/
View 37 Replies
View Related
May 1, 2007
Hello all!
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?
Thanks in advance...
View 3 Replies
View Related
Jan 16, 2008
Below you see a query I run.
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
fp.Security_ID Security_Name Bid_Price_Best_Latest Bid_Size_Best_Lates Bid_Source Ask_Price_Best_Latest Ask_Size_Best_Latest Ask_Source
1 Alli 84.0000 0.50 G NULL NULL NULL
2 bow 82.5000 0.50 G NULL NULL NULL
4 xte NULL NULL NULL 90.0000 0.50 G
6 Wqu 84.5000 0.50 I NULL NULL NULL
...
...
...
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)
CREATE TABLE [dbo].[tblPricesSourcesImportHistory](
[Price_Source_Import_History_ID] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [smallint] NULL,
[Source_Parent] [varchar](255) NULL,
[Source_Code] [varchar](255) NULL,
[Security_Name] [varchar](255) NULL,
[Source_Security_Name] [varchar](255) NULL,
[Bid_Price] [varchar](255) NULL,
[Bid_Size] [varchar](255) NULL,
[Ask_Price] [varchar](255) NULL,
[Ask_Size] [varchar](255) NULL,
[Import_Date] [smalldatetime] NOT NULL)
View 4 Replies
View Related
Nov 21, 2001
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)
View 1 Replies
View Related
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
View 3 Replies
View Related
Nov 1, 2006
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)
---------------------------------------------------------------------
select @begDay = dateadd(day,1,@begDay)
end
select * from @myTable
View 15 Replies
View Related
Jun 21, 2004
create table t1(a varchar(50) , b varchar(50))
create index i1 on t1(a)
create index i2 on t1(b)
create view v1
as
select * from t1 where isnull(a,b) = 'test'
select * from v1
The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ????
TIA
- ForXLDB
View 11 Replies
View Related
Oct 15, 2007
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)) '
View 12 Replies
View Related
Jan 30, 2005
Hi,
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
Result:
SeqNumber ObjId HierarchyLevel NameText
312text-12 social-1
413text-13 abc social-1.1
1135text-31 state-2 social-3
Could somebody help me please to optimize this query?
View 14 Replies
View Related
Jan 17, 2007
Hi i am using MS SQL 2000 db ,while executing following prepared statement query its working fine ...BUT when i remove "top 300" then tomcat giving OUT OF MEMORY ERROR!!
i think that its because of the cross join....So i want to optimize this query in such a way that it will not give "Out of memory error"
How can i re write this query?
Query :-
"select top 300 haw.DeviceID as deviceid,haw.Description as Description,haw.SerialNo as SerialNo,haw.Suffix as Suffix,haw.HWRev as HWRev,haw.Type as Type,dev.Status as Status from Hardware_PlaceHolder haw,Device_PlaceHolder dev ,Accounts ac where dev.siteId = ac.siteId and ac.CustID = ? ";
thanks in advance..
View 4 Replies
View Related
May 5, 2015
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id],Â
[Project1].[DirectoryId] AS [DirectoryId],Â
[Project1].[SPI] AS [SPI],Â
[Project1].[FirstName] AS [FirstName],Â
[Project1].[LastName] AS [LastName],Â
[Project1].[NPI] AS [NPI],Â
[Project1].[AddressLine1] AS [AddressLine1],Â
[Project1].[AddressLine2] AS [AddressLine2],Â
[code]...
View 5 Replies
View Related
Aug 21, 2006
SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
View 6 Replies
View Related
Jul 23, 2005
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Sep 25, 2006
Hi there
Running query analyzer against two different server.
the first only need 1-2 secs to return the query result,
the other return 7-8 secs for the query result.
plz advice what could cause this slow performance?
thx
View 1 Replies
View Related
Mar 8, 2007
This sounds like a pretty easy one. I have a SQL 2000 database with 2-3.4GHZ CPUs and 1GB of RAM. I have one database on it. I go in Query Analyzer on another machine and run a simple query like 'SELECT * FROM USERS' which should return 15,000 rows.
IT takes 30 (thirty) seconds to finish this query. OMG
Where do I start to decipher why on Earth this takes more than .01 seconds?
Thanks.
View 8 Replies
View Related
Mar 16, 2001
Hi,
I have a query which has suddenly started responding slow.
CAn anyone tell me what could be the possibilities?
I tried update stats(I am on sql 70-though it's done auto but i did it manually again)
I used union all in place of union but had no big effect.any othe thought?
Thanks!
View 4 Replies
View Related
Oct 12, 2000
Hi,
I have a query that takes minutes to execute, even through there are about 300,000 records are being processed. I would appreciate any help with optimizing that query.
I have two tables: User and Usage. Table user has two fields: User_Id and Date_Created and a non-clustered index on User_Id. Table usage has two fields also: User_Id and Date_Used and non-clustered index on both fields. The User table is populated when the user registers. The Usage table is populated every time the user opens a document.
Here is what I need to do: get the number of users from the Usage table who opened a document at least once after they have registered during the last 30 days for each day in the time frame, where the time frame varies.
For example, if the time frame is 8/01/00 - 8/31/00, I need to get the following data:
date returns
---- -------
8/01/00 10 (10 users returned to the document between 7/2/00 and 8/1/00)
8/02/00 15 (15 users returned between 7/3/00 and 8/02/00)
.
.
.
8/31/00 20 (20 users returned between 8/1/00 and 8/31/00)
Here is my query:
SELECT [date],
(SELECT count(distinct user_id)
FROM usage u JOIN [user] ON u.[user_id] = [user].[user_id]
WHERE u.[date] BETWEEN usage.[date]-30 AND usage.[date]
AND u.[date]>[user].date_created
GROUP BY usage.[date])returns
FROM usage
WHERE [date] BETWEEN @date1 AND @date2
This query works fine, but too slow. We use MS SQL server 7.0.
Thank you,
Yana
View 2 Replies
View Related
Dec 2, 2002
I have a query which responds immediately when run however if I add an order by clause it takes 40 seconds. Below is the query with the order by clause
SELECT distinct Licenseplate, platetypecode.platetypecode, platetypecode.platetypecodeid
FROM Ticket INNER JOIN PlateTypeCode
ON PlateTypeCode.PlateTypeCodeID = Ticket.PlateTypeCodeID
ORDER BY licenseplate
The Ticket table contains approx. 11,000 records. I have created a nonclustered index for the licenseplate field, a 7 char varchar field.
Any suggestions for speeding up the query?
View 6 Replies
View Related
Jun 15, 2004
The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?
Thanks in advance. Clive
SELECT
T1.APPT_REPT_FLG,
T18.X_ALIS_ID,
CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
T1.ASGN_USR_EXCLD_FLG,
T2.NAME,
T19.STAT_CD,
T1.APPT_REPT_TYPE,
T15.NAME,
CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
T1.TODO_CD,
T1.X_DOC_CAT_ID,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
T1.TARGET_OU_ID,
T7.ZIPCODE,
T3.ZIPCODE,
T9.EXP_RPT_NUM,
T1.LAST_UPD_BY,
T1.OWNER_PER_ID,
T1.PART_RPR_ID,
T1.RATE_LST_ID,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.ACTIVITY_UID,
T4.NAME,
T1.PR_TMSHT_LINE_ID,
T18.LAST_NAME,
T7.ADDR,
T18.SEX_MF,
T1.BILLABLE_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.SRA_SR_ID,
T1.TARGET_PER_ADDR_ID,
T18.X_FST_NAME,
T1.EVT_STAT_CD,
CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
T1.ROW_STATUS,
T1.ACD_CALL_DURATION,
T5.NAME,
T8.FAX_PH_NUM,
T8.X_FST_NAME,
T8.LAST_NAME,
T1.MODIFICATION_NUM,
T1.X_CAMP_ID,
CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
T1.ASSOCIATED_COST,
T13.NAME,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
T17.TMSHT_NUM,
T1.PR_SYMPTOM_CD,
T1.OPTY_ID,
CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
T1.PR_EXP_RPT_ID,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
T8.FST_NAME,
T16.SR_NUM,
T1.SRA_DEFECT_ID,
T1.CREATED_BY,
T8.WORK_PH_NUM,
CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
T1.CALL_ID,
T1.X_CLIENT_ID,
T1.PROJ_ID,
T12.DEFECT_NUM,
T1.CREATOR_LOGIN,
T1.CONFLICT_ID,
T19.OUTCOME_CD,
T1.TEMPLATE_FLG,
T2.PR_ADDR_ID,
T1.PREV_ACT_ID,
T1.X_DOC_NAME,
T1.EXP_RLTD_FLG,
T1.X_BATCH_REF,
T1.PRI_LST_ID,
T1.SRC_ID,
T1.X_POLICY_REF,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.EMAIL_FORWARD_FLG,
T11.DMT_NUM,
T1.TMSHT_RLTD_FLG,
T1.ROW_ID,
T10.NAME,
T18.CONSUMER_FLG,
T1.TARGET_PER_ID,
T18.FST_NAME,
T1.PRIV_FLG,
T3.PROVINCE,
T8.X_ALIS_ID,
T8.JOB_TITLE,
T14.NAME,
T1.NAME,
T1.PCT_COMPLETE,
T1.SRA_TYPE_CD,
T1.ALARM_FLAG,
T1.CAL_DISP_FLG,
T1.EVT_PRIORITY_CD,
T1.COST_CURCY_CD,
T2.LOC,
CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
T20.FILE_NAME,
T1.SRA_RESOLUTION_CD,
T6.PRDINT_ID,
T1.OWNER_LOGIN
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
WHERE
((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_SR_ID = '1-EQLOO')
View 3 Replies
View Related
Oct 12, 2006
I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.
I've simplified my actual queries for readability.
-- @filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO
DECLARE @filter INTEGER
SET @filter = NULL
-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @filter
When I run the query directly in Query Analyzer, it runs very fast.
DECLARE @filter INTEGER
SET @filter = NULL
-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
When I put the parameters in the stored proc it runs fast.
CREATE PROCEDURE queryPlayerStations
AS
DECLARE @filter INTEGER
SET @filter = NULL
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@filter IS NULL) OR (MyTable.Column = @filter))
GO
-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations
Anyone have any ideas what I can do to improve the stored proc case?
View 2 Replies
View Related
Mar 27, 2008
Hi guys/gals
I have only just signed up, as I have a problem thats confusing me a lot
I have a page (classic asp - sadly) that displays a list of invoices for a certain customer
Each of these customers has fairly complex permissions system, but the page generates a list of invoices and values in 0.01 seconds
However in the while...do loop, for each invoice it checks a database table for "extra charges"..
This is a simple SQL query
"Select Sum(amount) from extra_charges where invoice_number=" & current_invoice_number
However each time it runs this simple select statement it takes 2 seconds - which causes the page to load in 70 seconds instead of 0.01
I am absolutely beyond confused. I have tried it in a stored procedure, and a direct ado query..
The table by the way is empty on my current test system, and one one other system has about 150 records in it. Both take the same time
Any help would be appreciated?
This is a MSSQL 2000 database I've inherited, I am usually a PHP/MYSQL developer - so im a little out of my field of expertise here
View 13 Replies
View Related
May 26, 2004
i want to have a like search in the following query.
SELECT DISTINCT TOP 200 a.AccountID,
a.AccountNumber,
c.CLI,
con.SurName,
addr.Address1 [Account Address],
addr.Postcode as [Account Postcode],
atp.Name AS Type,
cs.Code AS Status
FROM account_t a
INNER JOIN customer_t cust on a.customerID = cust.CustomerID
INNER JOIN AccountType_T atp on cust.AccountTypeID = atp.AccountTypeID
INNER JOIN CustomerStatus_T cs ON a.CustomerStatusID = cs.CustomerStatusID
INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID
INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID
LEFT OUTER JOINCLI_T c ON a.AccountID = c.AccountID
WHERE (c.CLI LIKE @CLI + '%')
AND (con.SurName LIKE @Surname + '%')
AND (addr.Address1 LIKE @Address + '%')
AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%')
AND c.DateArchived IS NULL
here all fields @CLI, @Surname, @Address, @Postcode are varhcar types...which is making this query very slow...is there any suggestion to improve this query?
thanks in advance
bhavya
View 11 Replies
View Related
