Performance Optimization Of Search Query
Oct 16, 2006
I am facing some performance issues in a Stored Procedure. The procedure
needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
--get LOV details in table variables
INSERT INTO @tblLov (LovCode, LovDesc, ParamCode)
SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK)
WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus',
'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria
INSERT INTO @tblSearchResults
SELECT l1.LovDesc AS TransSource,
l2.LOVDesc AS CommTrailInd,
r.RemitCode as RemitNumber,
t.IntTransId as TransNumber,
CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate,
CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate,
rp.RepCode,
(ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName,
(CASE WHEN ISNULL(t.IntClntId,0)=0
THEN ISNULL(t.ClntShortNM, '') +
(CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' +
ISNULL(t.FundAcctNum,'')
WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'')
= 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN
' - ' + ISNULL(t.ClrHouseAcctNum,'')
ELSE '' END)
ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId)
END) AS Client,
(CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS
[Product],
t.InvAmt AS InvestmentAmt,
t.GDC AS GDC,
t.ClrChrg AS ClearingCharge,
t.NetComm AS NetCommission,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS
PayoutRate,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS
Payout,
l3.LOVDesc AS TransStatus,
t.Comments,
t.OrderMarkup AS BDMarkup,
t.IntTransId,
rp.IntRepId,
sch.SchCode,
t.IntClntId,
t.CUSIP,
t.RepIdValue AS RepAlias,
t.RepIdType,
t.SplitInd,
l4.LOVDesc AS AgencyPrincipalInd,
t.AgencyPrincipalFee,
t.EmployeeTradeInd,
t.ShareMarkup,
t.UnitsTraded,
s.SponsorNm,
CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production
Defect #873 & 877
WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') =
'VA') THEN ISNULL(t.PolicyNum,'')
ELSE t.ClrHouseAcctNum END,
CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined'
ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType,
l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus,
t.TransAuditStatus AS TransAuditStatusCode,
t.OriginalTransId,
t.RowId,
t.Status,
t.intParentTransId,
t.CancelTrdInd,
t.ClrChrgOverrideInd,
9999 AS AuditKey
FROM tr_CommTrans t WITH (NOLOCK)
INNER JOIN @tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc'
INNER JOIN @tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode =
'CommTrailInd'
INNER JOIN @tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode =
'CommTxnStatus'
INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId
LEFT OUTER JOIN @tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and
l4.ParamCode = 'AgencyPrincipalInd'
LEFT OUTER JOIN @tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND
l5.ParamCode = 'AuditTransStatus'
LEFT OUTER JOIN @tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode =
'ProdSubType'
LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP
LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId =
s.IntBDSponsorId
LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId
LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId
WHERE t.IntTransId = (CASE WHEN @intTransId IS NULL THEN t.intTransId ELSE
@intTransId END) AND
t.TransSrc = @strTransSrc AND
r.RemitCode = (CASE WHEN ISNULL(@strRemitCode,'')='' THEN r.RemitCode ELSE
@strRemitCode END) AND
ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @dtmFromSettlementDt AND
@dtmToSettlementDt AND
ISNULL(t.TrdDt,'01-01-1900') BETWEEN @dtmFromTradeDt AND @dtmToTradeDt AND
t.CommTrailInd = (CASE WHEN @chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C'
END) AND
t.Status = (CASE WHEN ISNULL(@strStatus,'')='' THEN t.Status ELSE
@strStatus END) AND
ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')=''
THEN ISNULL(t.ClrHouseAcctNum,'')
WHEN (@strTransSrc = 'PSH' OR @strTransSrc = 'MSR' OR @strTransSrc
= 'MSA') THEN @strAccountId
ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND
ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.FundAcctNum,'')
WHEN @strTransSrc = 'NSM' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN
@strAccountId
ELSE ISNULL(t.FundAcctNum,'') END) AND
ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.PolicyNum,'')
WHEN @strTransSrc = 'NSV' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN
@strAccountId
ELSE ISNULL(t.PolicyNum,'') END) AND
ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @intSponsorId IS NULL THEN
ISNULL(t.IntBDSponsorId,-1) ELSE @intSponsorId END) AND
ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@strProdType,'')='' THEN
ISNULL(t.ProdType,'') ELSE @strProdType END) AND
ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@strProdSubType,'') ='' THEN
ISNULL(t.ProdSubType,'') ELSE @strProdSubType END) AND
ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@strCUSIP,'')='' THEN
ISNULL(t.CUSIP,'') ELSE @strCUSIP END) AND
ISNULL(rp.SSN, 0) = (CASE WHEN @numRepSSN IS NULL THEN ISNULL(rp.SSN, 0)
ELSE @numRepSSN END) AND
ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@strRepCode,'')='' THEN
ISNULL(rp.RepCode,'') ELSE @strRepCode END) AND
ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@strRepLstNm,'')='' THEN
ISNULL(rp.LstNm,'') ELSE @strRepLstNm END) AND
ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@strRepFstNm,'')='' THEN
ISNULL(rp.FstNm,'') ELSE @strRepFstNm END) AND
ISNULL(rp.RepStatus,'') <> (CASE WHEN @chrIncludeTerminated='Y' THEN 'Z'
ELSE 'T' END) AND
ISNULL(t.IntClntId,-1) = (CASE WHEN @intClientId IS NULL THEN
ISNULL(t.IntClntId,-1) ELSE @intClientId END) AND
( (@chrAuditReportFlag = 'N' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded, @strLov_TxnStatus_Cancelled) AND
ISNULL(TransAuditStatus,@strLov_TransAuditStatus_Active) =
@strLov_TransAuditStatus_Active
)
OR
(@chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@strLov_TransAuditStatus_Active, @strLov_TransAuditStatus_Cancelled)
)
)
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I have a table with 3M rows that contains a varchar(2000) field withvarious keywords. Here is the table structure:PKColumnImageIDFullTextColumnThere is an association table:ImageIDContractIDNow, I want to do a query where the ContractID = x and Contains someword in the FullTextColumn. There is an association table that mapsImages to Contracts - so I can't use the trick of putting the Contractcode in the FullTextColumn.I'm finding that first the FTS service is performing a search on theKeyword (which can take a long time if 100K rows are returned) thenjoining to the association table for the particular contract.Is there anyway to make this faster by telling the FTS service, onlysearch this subset of rows for the keyword based on the contract.Sorry if this sounds convoluted. Appreciate any help you can suggest.Thanks!
View 1 Replies
View Related
Apr 30, 2007
Hi,
I have Search Critieria which makes use of "LIKE" statement to get records.THis is very simple search just making use of LIKE statment on two Columns of the table.
Example : SELECT ID from tblName WHERE ID LIKE '%PID_01%' AND LID LIKE '%CR_03%'
This works fine and also performace is also good when we have hundreds/thousandsof records.
But when records is of lakhs,i feel using LIKE statment will reduce the performance of our search Query.
SO how can we good performance in search ...?
I need to optimize my search which result good performace when we have lakhs of records ....?
View 1 Replies
View Related
Jun 10, 2008
Hi,
I have recently taken up performance optimization activity for our database. Can any one suggest a really good source for articles/tutorials/guides etc. on Performance optimization for SQL server 2005.
Regards
Yogi
View 4 Replies
View Related
Oct 5, 1998
Does anyone know any good references (web, books etc) about optimizing performance for MSSQL Server 6.5, not seen from a developers perspective but more the admin of the SQL Server?
I`m using a very data comprehensive application and I have the feeling things would run a lot smoother if the database/server was optimized once in a while (like you can do with Access).
Anyone?
View 3 Replies
View Related
Sep 27, 2006
Hi,
our package have design like this,
OLEDBSource à Derived Column à Lookup
|
Matching Records Un Matched Records
| |
OLEDBCommand OLEDBDestination
(Update) (Insert)
and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package
Jegan
View 3 Replies
View Related
Oct 3, 2007
I am able to get this to work by using nested loops but they are very inefficient and with the size of my tables I cannot afford to use them. There must be a more efficient solution?
I have two tables...
Initial_Procedure
ID
Person_ID
Completed_DTTM
Procedure_2
ID
Person_ID
Completed_DTTM
I need the result to be...
Tbl_Final_Result
Initial_Procedure.ID
Initial_Procedure. Person_ID
Initial_Procedure. Completed_DTTM
Procedure_2.ID
Procedure_2.Person_ID
Procedure_2.Completed_DTTM
Some general rules...
(Hope these are clear enough)
- Each person has at least one Initial_Procedure.
- There may be zero, one, or more Procedure_2 for each Initial_Procedure.
- If there is more than one Procedure_2 for an Initial_Procedure get the most recent.
- To link Procedure_2 to Initial_Procedure the Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM and Initial_Procedure.Person_ID = Procedure_2.Person_ID
- If there is more than one Initial_Procedure where Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM:
Procedure_2.Completed_DTTM Between row 1: Initial_Procedure.Completed_DTTM and
row 2: Initial_Procedure.Completed_DTTM --(assuming Initial_Procedure is in order)
AND Initial_Procedure.Person_ID = Procedure_2.Person_ID
Some example data.....
Declare @Initial_Procedure table (ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Initial_Procedure
Select 1, 1, '01/10/2007' union all
Select 1, 1, '02/15/2007' union all
Select 1, 1, '02/20/2007' union all
Select 1, 2, '01/02/2007' union all
Select 1, 3, '06/26/2007' union all
Select 1, 4, '03/14/2006' union all
Select 1, 4, '10/10/2006' union all
Select 1, 4, '08/27/2007'
Declare @Procedure_2 table( ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Procedure_2
Select 2, 1, '01/09/2007' union all
Select 2, 1, '01/15/2007' union all
Select 2, 1, '01/16/2007' union all
Select 2, 1, '01/17/2007' union all
Select 2, 1, '02/19/2007' union all
Select 2, 1, '07/25/2007' union all
Select 2, 1, '09/02/2007' union all
Select 2, 2, '01/01/2007' union all
Select 2, 2, '01/14/2007' union all
Select 2, 2, '01/20/2007' union all
Select 2, 3, '05/04/2007' union all
Select 2, 3, '06/27/2007' union all
Select 2, 4, '11/06/2006'
The final result should be...
Tbl_Final_Result
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
1 1 01/10/2007 2 1 01/17/2007
1 1 02/15/2007 2 1 02/19/2007
1 1 02/20/2007 2 1 09/02/2007
1 2 01/02/2007 2 2 01/20/2007
1 3 06/26/2007 2 3 06/27/2007
1 4 03/14/2006 NULL NULL NULL
1 4 10/10/2006 2 4 11/06/2006
1 4 08/27/2007 NULL NULL NULL
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a question regarding stored procedure desing that provides theoptimal performance. Let's say we have a table Products that consists ofthree columns: Name, Status, RegistrationTime. All columns are indexed andusers should be able to lookup data by any of the columns. We have two mainoptions to design stored procedures for data retrieval:1. Design separate stored procedures for each search criteria:LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.2. Write a generic stored procedure that will fit any search criteria:CREATE PROCEDURE GetProducts (@Name varchar(20),@Status int = NULL,@FromTime datetime = NULL,@ToTime datetime = NULL)AS BEGINSELECT[Name],[Status],[RegistrationTime]FROM [Products]WHERE [Name]=CASEWHEN @Name<>NULL THEN @NameELSE [Name]ENDAND [Status]=CASEWHEN @Status<>NULL THEN @StatusELSE [Status]ENDAND [RegistrationTime]>=CASEWHEN @FromTimestamp<>NULL THEN @FromTimestampELSE [RegistrationTime]ENDAND [RegistrationTime]<=CASEWHEN @ToTimestamp<>NULL THEN @ToTimestampELSE [RegistrationTime]ENDORDER BY [RegistrationTime]END;The second option is very attractive, because it is obviously easier tomaintain such code. However, I am a little concerned about performance ofsuch stored procedure. It is not possible to foresee what index should beused, index can only be selected each during procedure execution, becausesearch criteria can include either Name, Status or RegistrationTime. Will itmake this SP inefficient? Or perormance difference in such case is not big(if any) and we should choose the second option because of its significantcode reduction?Thanks in advanceVagif AbilovJoin Bytes!
View 3 Replies
View Related
May 3, 2007
Hi, all experts here,
Would any of you give me any ideas for how could we optimize the report on data mining models? (as we know, for the data mining report, we have to select the mining model and the case table)
Hope it is clear for your advices and help.
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Nov 23, 2004
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
View 8 Replies
View Related
Aug 15, 2007
Hi,
Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.
DECLARE @EmpID, @DateFrom, @Total1 .... // Declaring different variables
SELECT @DateFrom = // Set to start of any month e.g. 2007-06-01 ...... 1st
Loop (condition -- Get all employees, working fine)
BEGIN
SELECT @EmpID = // Get EmployeeID ...... 2nd
SELECT @Total1 = SUM (Abences) ...... 3rd
FROM Attendance
WHERE employee_id_fk = @EmpID (from 2nd step)
AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @DateFrom)) (from 1st step)
SELECT @Total2 ........................... same as above
SELECT @Total3 ........................... same as above
INSERT IN @TABLE (@EmpID, @Total1, ...... @Total31) ...... 4th
Iterate (condition) to next employee ...... 5th
END
It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me....
View 11 Replies
View Related
Feb 19, 2001
Trying to optimize a query, and having problems interpreting the data. We have a query that queries 5 tables with 4 INNER JOINS. When I use INNER HASH JOIN, this is the result:
(Using SQL Programmer)
SQL Server Execution Times:
CPU time = 40 ms, elapsed time = 80 ms.
Table 'Table1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0.
Table 'Table3Category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
When I use INNER JOIN, this is the result:
SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 34 ms.
Table 'Table1'. Scan count 4, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 311, logical reads 670, physical reads 0, read-ahead reads 0.
Table 'Table3'. Scan count 69, logical reads 102, physical reads 0, read-ahead reads 0.
Table 'Table4'. Scan count 69, logical reads 98, physical reads 0, read-ahead reads 0.
Table 'Table5'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
Now, when timing the code execution on my ASP page, it's "faster" not using the HASH. Using HASH, there are a few Hash Match/Inner Joins reported in the Execution Plan. Not using HASH, there are Bookmark Lookups/Nested Loops.
My question is which is better to "see": Boomark Lookups/Nested Loops or Hash Match/Inner Joins for the CPU/Server?
Thanks.
View 1 Replies
View Related
Mar 14, 2003
IS there any way to rewrite this Query in optimized way?
SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))
Thanks....j
View 1 Replies
View Related
Mar 7, 2006
How can I optimized the following query:
(SELECT e.SID
FROMStudents s
JOINTable1e ON e.SID= s.SID
JOINTable2 ed ON ed.Enrollment = e.Enrollment
JOINTable3 t ON t.TNum = e.TNum
JOINTable4 bt ON bt.TNum = t.TNum
JOINTable5 b ON b.Batch = bt.Batch
JOIN IPlans i ON i.IPlan = ed.IPlan
JOINPGroups g ON g.PGroup= i.PGroup
WHERE t.TStatus= 'ACP'
ANDed.EStatus= 'APR'
ANDe.SID=(select distinct SID from Table1 where Enrollment=@DpEnrollment))
AND(ed.EffectiveDate=
(SELECT EffectiveDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
ANDTCoord = @DpTCoord
ANDAGCoord= @DpAGCoord
ANDDCoord=@DpDCoord )
ANDDSeq= @DpDSeq)
ANDe.SID=
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
ANDed.TerminationDate=
(SELECT TerminationDate
FROM Table2 ed JOIN Table1 e
ON e.enrollment=ed.enrollment
WHERE IPlan = @DpIPlan
ANDTCoord = @DpTCoord
ANDAGCoord= @DpAGCoord
ANDDCoord= @DpDCoord )
ANDDSeq= @DpDSeq)
ANDe.SID=
(select distinct SID from Table1 where Enrollment=@DpEnrollment))
)
))
View 2 Replies
View Related
Mar 20, 2006
DECLARE @PTEffDate_tmp AS SMALLDATETIME
SELECT @PTEffDate_tmp = DateAdd(day, -1, PDate)
FROM PDates pd WHERE iplan = @DIPlan and pd.TCoord = @DTCoord and DType = 'EF'
DECLARE @PTCoord_tmp as char(3)
SELECT @PTCoord_tmp = tc.TCoord
FROM PDates pd JOIN TCoords tc ON (pd.TCoord = tc.TCoord)
WHERE pd.Iplan = @DIPlan and tc.TGroup = @TGroup_tmp
and PDate = @PTEffDate_tmp and DateType = 'TR1'
DECLARE @EStatus_tmp as char(3)
SELECT @EStatus_tmp = EDStatus From EDetails ed
JOIN ENR e ON (ed.enr = e.enr)
JOIN Trans t ON (e.transID = t.TransID)
WHERE iplan = @DIPlan
and ed.TCoord = @PTCoord_tmp
and t.TransS= 'ACP'
and DCoord = @DCoord
and CEnr is null
View 3 Replies
View Related
Aug 17, 2006
How can I optimazed my query. Since my DB is more then 1 mln it takes a while to do all those join?
select *
FROM EEMaster eem
JOIN NHistory nh
ON eem.SNumber = nh.SNumber
OR eem.OldNumber = nh.SNumber
OR eem.CID = (Replicate ('0',12-len( nh.SNumber))+ nh.SNumber )
View 4 Replies
View Related
Apr 23, 2008
I work on tables containing 10 million plus records.
What are the general steps needed to ensure that my queries run faster?
I know a few:
- The join fields should be indexed
-Selecting only needed fields
-Using CTE or derived tables as much as I can
-Using good table reference
eg
select a.x , b.y
from TableA a inner join TableB b
on a.id = b.id
I will be happy if somebody could share or add more to my list.
Regards to all
View 4 Replies
View Related
May 1, 2008
Dear all,
The below query take 7 min to execute so i want optimize the query.please any suggestions..........
SELECT DISTINCT VC.O_Id C_Id, VC.Name C_Name,VB.Org_Id B_Id,
VB.code S_Code,VB.Name S_Name, mt12.COLUMN003 M_D_Code,
mt12.COLUMN004 M_D_Name,CQ.COLUMN004 R_Code,
CQ.COLUMN005 R_Date, CQ.COLUMN006 Ser,CQ.COLUMN008 R_Nature,
CQ.COLUMN011 E_Date,mt26.COLUMN003 W_Code, mt26.COLUMN004 W_Name,
mt17.COLUMN005 V_Code,mt17.COLUMN006 V_Name, mt19.column002 I_Code,
mt19.column003 I_Name, mt19.COLUMN0001 R_I_No,mt92.COLUMN001 B_Id,
mt92.COLUMN005 B_No, CASE mt92.COLUMN006 WHEN '0' THEN 'Ser'
WHEN '1' THEN 'Un-Ser' WHEN '2' THEN 'Ret' WHEN '3' THEN 'Retd'
WHEN '4' THEN 'Rep' WHEN '5' THEN 'Repd' WHEN '6' THEN 'Con'
WHEN '7' THEN 'Cond' ELSE mt92.COLUMN006 END S_C_Type,
mt20.COLUMN003 T_G_Code,mt20.COLUMN004 T_G_Name, V.U_Code,V.U_Name,
mt19.column005 I_Quantity,mt20.COLUMN003 T_Code, mt20.COLUMN004 T_Name,
mt59.COLUMN005 T_Price,VR.code C_L_Code,
VR.Name C_L_Name
FROM tab90 CQ
INNER JOIN tab91 mt19 ON mt19.COLUMN002 = CQ.COLUMN001
LEFT JOIN tab92 mt92 ON mt92.COLUMN002 = CQ.COLUMN001
LEFT JOIN tab93 mt93 ON mt93.COLUMN004 = CQ.COLUMN001
INNER JOIN tab12 mt12 ON mt12.COLUMN001 = CQ.COLUMN003
LEFT JOIN tab26 mt26 ON mt26.COLUMN001 = CQ.COLUMN009
LEFT JOIN tab20 mt20 ON mt20.COLUMN001 = mt93.COLUMN005
LEFT JOIN tab59 mt59 ON mt59.COLUMN002=mt20.COLUMN001
LEFT JOIN tab17 mt17 ON mt17.COLUMN001 = CQ.COLUMN010
INNER JOIN VM V ON V.UOM_ID = mt19.COLUMN004
INNER JOIN tab19 mt19 ON mt19.COLUMN001 = mt19.COLUMN003
INNER JOIN vOrg VR ON CQ.COLUMN007 = VR.Org_Id
INNER JOIN vOr VB ON CQ.COLUMN002 = VB.Org_Id
INNER JOIN vOr VC ON VB.Top_Parent = VC.Org_Id
WHERE CQ.COLUMN005 Between '02/01/2007' and '08/25/2008' And VC.O_Id in ('fb243e92-ee74-4278-a2fe-8395214ed54b')
Thanks&Regards,
Msrs
View 4 Replies
View Related
Jun 18, 2008
Hi All,
table with initial data:
Primary key (COL1 + COL2)
COL1 COL2 NEW LATEST
1241 1 1
125 0 1 1
by default, NEW and LATEST columns will have values 1, 1.
Now, one row is inserted with values (124,2)
Table data should be:
COL1, COL2, NEW, LATEST
1241 1 0
125 0 1 1
1242 0 1
LATEST column value changes for Row 1 since there is a repetition of value 124, meaning this row is no longer the latest.
NEW COLUMN value changes for ROW 2 since there it is no longer new; we already have an occurrence of 124 in the first row.
I m not sure if i can solve this query using any option other than cursor. it will be like taking first row --> comparing it with all the other rows and then moving further.
Plz. suggest me if there is a better approach for doing this
View 11 Replies
View Related
Mar 12, 2007
Okay guys, this will probably be messy. Just throw out some thoughts and I'll deal with it. How do I make this query smaller and more efficient?
Query deleted and link posted: http://theninjalist.com/
View 4 Replies
View Related
Apr 9, 2008
Hello!
I have a query:
SELECT *,
.....
(SELECT add_house
FROM hs_address
WHERE add_id = do_address_registration_id) as add_house,
(SELECT add_flat
FROM hs_address
WHERE add_id = do_address_registration_id) as add_house,
.....
FROM hs_donor
WHERE do_id = 400
Fields add_flat and add_house belong to one table. How one may optimize this query?
P.S. do_address_registration_id can be equal NULL
TIA
View 1 Replies
View Related
Mar 11, 2008
I am writing a query which will display employee details who is handling maximum number of projects.
Here I am joining 2 tables. one is LUP_EmpProject, which contain employee id and project id and project date, in this table I have used a composite primary key of employee id, project id and project date. The other table is
EmployeeDetails which contain employee names and employee id.
I want to display the details of the employee who is handling maximum projects.
Below given is the code which is working fine. But the query is taking time to execute it. Any body know how to optimize the code so that I can get the result quickly.
Code Snippet
SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.Empid=EmployeeDetails.Empid
GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName,
LUP_EmpProject.Empid
HAVING COUNT(LUP_EmpProject.Empid)>0
AND COUNT(LUP_EmpProject.Empid)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub)
Please help!!!!!!!!!!
View 6 Replies
View Related
Aug 14, 2007
My Query is like this..
set @Grouptitle = @GroupPFR
set @GroupOrder = 5
set @Unittype = 2
set @MetricName = 'Product to Net Revenue %'
set @MetricOrder = 6
insert into @FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)
select @GroupTitle,@MetricName,@UnitType,f1.weekdate,
max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,
@GroupOrder,@MetricOrder --from @temptable
from @FinalData f1 inner join @FinalData f2 on f1.weekdate = f2.weekdate
where (f1.Grouptitle = @GroupPFR and f1.MetricName = '$ Products')
and ( f2.Grouptitle = @GroupRevenue and f2.MetricName = 'Net Revenue')
group by f1.weekdate
There are many calculations like this in my procedure.
and It takes like 3 min to run whole procedure
now as I am doing group by..
So In Execution plan it show me that 60% of the query time is take n by SORT operation..
can any one give me any other option to do this.
Thanks
View 9 Replies
View Related
Dec 5, 2007
Hi all,
I have the following query to be optimized. It just takes too long to complete the execution.
----------------------------------------------------------------------------------
SELECT COUNT(*)
FROM Tbl_A a
INNER JOIN Tbl_B b
ON a.AID = b.AID
INNER JOIN Tbl_C c
ON a.AID = c.AID
INNER JOIN Tbl_D d
ON d.DID = a.DID
INNER JOIN Tbl_E e
ON e.DID = d.DID
INNER JOIN Tbl_F f
ON e.EID = f.EID
WHERE a.Col_1 = 1
AND (a.Col_2 LIKE N'%abc%')
AND a.Col_3 <>
CASE
WHEN d.Col_1 ='ABC' THEN 'BR'
ELSE ''
END
AND c.Col_1 =
CASE
WHEN d.Col_1 ='ABC' THEN 'ABC_COMPANY'
ELSE 'PPRO'
END
AND f.Col_1 = 'val1'
------------------------------------------------------------------------------------------------------------------
here is the estimated records for the tables.
------------------------------------------------------------------------------------------------------------------
Tbl_A has over 150,000 records
Tbl_B has over 150,000 records
Tbl_C has over 450,000 records
Tbl_D has over 33 records
Tbl_E has over 4000 records
Tbl_F has over 5000 records
------------------------------------------------------------------------------------------------------------------
Thanks in advance,
Soe Moe
View 5 Replies
View Related
Apr 10, 2008
Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
View 4 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Feb 5, 2007
I need help in optimizing this query. The major time takes in calling a remote database. Thanks in advance.ALTER PROCEDURE dbo.myAccountGetCallLogsTest@directorynumber as varchar(10),@CallType as tinyint ASdeclare @dt as intSELECT TOP 1 @dt=datediff(day,C.EstablishDate,getdate())FROM ALBHM01CGSERVER.Core.dbo.Customer C INNER JOIN ALBHM01CGSERVER.Core.dbo.UsgSvc U ON C.CustID = U.CustIDWHERE (U.ServiceNumber = @directoryNumber)ORDER BY C.EstablishDate DESCIF @dt>90select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time] from vw_Call_Logs where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())<90order by starttime descELSE select DN as Number, Remote_DN as [Remote Number], City, StartTime as [Start Time], EndTime as [End Time] from vw_Call_Logs where DN = '1' + @directoryNumber and call_type = @CallType and datediff(day,starttime,getdate())< @dtorder by starttime desc
View 13 Replies
View Related
Mar 28, 2008
Hi , How to optimize sql query in sql server 2005, any idea
View 4 Replies
View Related
May 2, 2002
i have query similar to this:
select count(a.callid) from tbl1 as a
inner join tbl2 as b on a.calldefid=b.calldefid
where a.programid=175
select count(a.callid) from tbl1 as a
inner join tbl2 as b on a.calldefid=b.calldefid
where b.programid=175
callid - pk on tbl1
calldefid - nonclustered index on both tbl1 and tbl2
programid - nonclustered index on both tbl1 and tbl2
tbl2 is the smaller table
from my understanding, the second query will run faster because you reduce the records in the smaller table, then join to the larger table (tbl1).
but can you explain to me why limiting the rows on tbl1 first, then joining to tbl2 would take longer?
View 1 Replies
View Related
Apr 11, 2008
hi guys
i have a query which will take lot of load on the sql server and it need to optimized.
can any one suggest in optimizing the below query.
SELECT TOP 1 p.store_purchase_id
FROM valid_purchase p WITH (NOLOCK)
INNER JOIN purchase_item pitm WITH (NOLOCK)
ON p.purchase_id = pitm.purchase_id
INNER JOIN customer_phone c WITH (NOLOCK)
ON p.customer_brand_id = c.customer_brand_id
INNER JOIN phone ph WITH (NOLOCK)
ON c.phone_id = ph.phone_id
WHERE
p.last_name = ''
AND pitm.item_datetime > CURRENT_TIMESTAMP
AND pitm.item_datetime <= DATEADD(d, 12, CURRENT_TIMESTAMP)
AND REPLACE(ph.number, '-', '' ) = ''
Thanks
Ramakanth
View 5 Replies
View Related
Jul 23, 2005
Hello Eveybody,I have just joined as DBA.My problem will be simple for you allgurus.I have a table with 50 lakh row,when i am writing a selectstatement for this table it takes around 7 minute to display result.Iused DBCC REINDEX for this table but it was not effective.So please pass ur suggestion that i should follw to reduce myexecution time.As its effecting my server performance.Thank u in advancevv
View 2 Replies
View Related
