Query Optimization - Joining A View And A Table

Apr 5, 2006

I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join €“ it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.
So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.
It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different €“ it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.

Any suggestions will be appreciated greatly
Stoil Pankov

"vHCItemLimitUsed" - this is the view
"tHCContractInsured" - this is the table
"ixHCContractInsuredID" - is the primary key of the table

Here is a simple representation of the effect:

Version 1:
select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)


Version 2:
select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)

Version 3:
select *
from dbo.vHCItemLimitUsed
where ixHCContractInsuredID in
(select ixHCContractInsuredID
from tHCContractInsured
where ixHCContractInsuredID in (9012,9013,9014,9015))

View 1 Replies


ADVERTISEMENT

View And Query Optimization

Mar 23, 2006

I'm have made a series of tables and views to create some reports. I have included boiled-down versions of them below. The report query that I want to run (at the bottom) takes a long time to execute and sometimes times out. Creating a rank within the query is doing the most damage, but it can still take a long time to execute without it. The usage table has almost 70k records and there are over 10k users. Does anyone have suggestions for making these views/queries more efficient?

==========================

TABLE TGroupAudit
AuditID, intOldGroupID, intNewGroupID, strUserID, dtJoinDate

==========================

TABLE people
strUserID, name

==========================

TABLE usage
UsageID, strUserID, cost, seconds, gmttime, accesstype

==========================

CREATE VIEW VUsageSubtotals AS
SELECT TOP 100 PERCENT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, usage.accesstype, SUM(usage.cost) AS subtotal, MONTH(usage.gmttime) AS [month], VUsageTotals.total
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.strUserID INNER JOIN
VUsageTotals ON VUserGroupDates.strUserID = VUsageTotals.strUserID AND
VUserGroupDates.intGroupID = VUsageTotals.intGroupID AND MONTH(usage.gmttime) = VUsageTotals.[month]
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, VUserGroupDates.intGroupID, usage.accesstype, MONTH(usage.gmttime), VUsageTotals.total

==========================

CREATE VIEW VUsageTotals AS
SELECT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, SUM(usage.seconds) AS totaltime, SUM(usage.cost) AS totalcost, MONTH(usage.gmttime) AS [month]
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.userid
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, MONTH(usage.gmttime)

==========================

CREATE VIEW VUserGroupDates AS
SELECT strUserID, intNewGroupID AS intGroupID, dtJoinDate,
ISNULL ((SELECT MAX(TGA2.dtJoinDate) FROM TGroupAudit TGA2
WHERE TGA2.dtJoinDate < TGA1.dtJoinDate AND TGA2.strUserID = TGA1.strUserID), GETDATE()) AS dtLeaveDate
FROM TGroupAudit TGA1

==========================

Here's a sample query that gets run for a report:

SELECT TOP 30 PERCENT WITH TIES V1.*,
(SELECT COUNT(DISTINCT V2.subtotal)
FROM VUsageSubtotals V2 WHERE V1.subtotal <= V2.subtotal
AND V1.intGroupID = V2.intGroupID
AND V2.month = 3) AS rank
FROM VUsageSubtotals V1
WHERE strUserID IN ('a1, a2, b5, b7, b9, d12')
AND V1.month = 3
AND accesstype = 'STANDARD'
GROUP BY strUserID, intGroupID, subtotal, month, total, accesstype

The purpose of the report is an accounting one. It is desired to know how much each user used each accesstype during a month. Usage is further split up by the user's groups (which may never change or could several times during a month) for billing purposes.

View 5 Replies View Related

Optimization Of Query On Partitioned View

May 27, 2008

Schema below.

The execution plan shows that this query is correctly optimized to check only the underlying Employee_2008 table.

select * from Employee where ReportingYear = '2008'

This query is not optimized and checks both Employee_2008 and Employee_2007:

declare @ry varchar(4)
set @ry = '2008'
select * from Employee where ReportingYear = @ry

How can I get second query to be optimized correctly?

Schema:

CREATE TABLE [dbo].[Employee_2007](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Employee_2008](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE VIEW [dbo].[Employee]
AS
SELECT
'2007' ReportingYear,
EmployeeID,
Name
FROM Employee_2007

UNION ALL

SELECT
'2008' ReportingYear,
EmployeeID,
Name
FROM Employee_2008

View 3 Replies View Related

Joining N-table In View Or Use N-stored Procedure?

Nov 14, 2007

I have three table and I have to fetch some data from each one. This can be done by calling three diffrent stored procedures for each one.But it can be done with view and joining these three tables and only one time calling this view and getting the same result.(These joins can be from diffrent database too)

Which one is better View and joining these three tables and call this view one time or calling three stored procedures in for example .net side.


Sincerely

Kianoosh

View 7 Replies View Related

Joining Large Fact Table To A View That Returns 120 Rows

Jan 19, 2015

I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.

Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]

I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.

Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?

View 9 Replies View Related

Query Optimization Via Table Properties?

Jul 26, 2006

Query #1:select <list of fields>from Cjoin B on C.b_key = B.b_keyjoin A on B.a_key = A.a_keywhere A.o_key = <some value>Query #2:select <list of fields>from Cwhere b_key in (select b_keyfrom Bwhere a_key in (select a_keyfrom Awhere o_key = <some value>))#1 (and other things with the same general pattern) are used inliterally thousands of places in this one client's system, and ismuch nicer to write, but seems to be rather slower than #2. Isthere any way to tweak the tables to tell the system somethinglike "hey, B, whenever you're joined to A, you should seriouslyconsider waiting for A to be filtered down to a manageable levelfirst"? And similarly for C/B.MS SQL 2000, SP3, 6.5 compat mode. These are set in stone untilwe upgrade the accounting software (highly non-trivial).

View 1 Replies View Related

Table Data Retrieval And Optimization Optimization Help

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

View Optimization?

Jul 23, 2005

I just resolved a strange situation I was having with an ODBC linked SQL 7view in an Access 2000 MDB file, and I'm trying to get some understanding asto what happened.The linked view was scrolling very slowly in the MDB file; however, itscrolled very quickly when accessed through an ADP file. Theoretically,since it's a server-side object, it should appear the same in both files,but it didn't.Not knowing what was causing this, I tried various options. When I decidedto try encrypting the view, I first made a copy of it, and encrypted thecopy, and linked that copy to the MDB file. It scrolled without hanging asthe original one did.But, strangely enough: once I encrypted the copy of the view, the originalview also now scrolled quickly (about 5-10 times faster than it had been). Ieven deleted the encrypted copy, and the original view still scrolled veryquickly in the MDB file.So, it seems that there was some sort of compile/optimization issue thatkicked in when I encrypted the copy of the view -- and not just for that oneview, but for the whole database (or at least the original view and itscopy). Whatever happened, it resolved the problem with the view hanging whenscrolled in the MDB file.So, my questions are: a) what happened? and b) is there a way to getwhatever happened to happen without having to make a copy of a view, encryptit, and then delete it??Thanks for any assistance.Neil

View 4 Replies View Related

Joining Multiple Tables In A View.

Dec 5, 2005

I have three tables
 
1st table is Student
StudnetID (pk)
Other fields…
 
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
 
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
 
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
 
StudnetID: 1
Name:  John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager:   123-456-7890
Fax:
Memo: This is one student record.
 
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover

View 3 Replies View Related

Joining Tables In A VIEW SELECT Statement

Jan 11, 1999

Wuddup,

I am trying to create a view that encapsulates some specific info from many different tables. I have about 30 tables all with exactly the same field names and field types. I want to take 3 of the fields from every table and put them together into one 'VIEW' so I can run more efficient queries. SQL however, doesn't let you 'combine' 2 columns from different tables into one column in the view. (making sense?)
I tried running a 'UNION' but you are specifically NOT allowed to run a union in a create view statement. Anyone have any ideas?

View 1 Replies View Related

Stored Procedure Query Optimization - Query TimeOut Error

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

Inserting Into A Tmp Table Using A View --- Please Help Using SQL Query Analyzer

Nov 30, 2006

Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME


Quote:

View 4 Replies View Related

Query Optimization - Please Help

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

Query Optimization

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

Query Optimization

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

Query Optimization

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

Query Optimization

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

Query Optimization

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

Query Optimization

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

Query Optimization

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

SQL Query Optimization

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

Query Optimization

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

Query Optimization

Oct 16, 2007

Dear all,

View 15 Replies View Related

Query Optimization

Nov 2, 2007

Dear all,

View 6 Replies View Related

Query Optimization

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

Query Optimization

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

Optimization Of Query

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

Need Help On Query Optimization

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

Application Role How Query A View Whose Reference Table Is In Another Db?

Aug 23, 2007

Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()

I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:


The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania

View 5 Replies View Related

Sql Query Optimization Help Needed

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

Query Optimization In Sql 2005

Mar 28, 2008

 Hi ,        How to optimize sql query in sql server 2005, any idea 

View 4 Replies View Related

Query Optimization - Confused!

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

Urgently Need The Query Optimization

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







Copyrights 2005-15 www.BigResource.com, All rights reserved