Optimized Qeries Without Using Sub-Correlated Queries

Aug 16, 2007

Hi, 
I am used to writing Sub-Correlated queries within my main queries. Although they work fine but i have read alot that they have performance hits. Also, as with time our data has increased, a simple SELECT statement with a few Sub-Queries tends to run slower which may be between 10-15 seconds. Following will be a simple example of what i mostly do:  
SELECT          DISTINCT C.CusID, C.Name, C.Age, 
                        (
                                    SELECT          SUM  (Price)
                                    FROM             CusotmerOrder
                                    WHERE           CusID_fk = CO.CusID_fk
                        )           Total_Order_Price, 
                        (
                                    SELECT          SUM (Concession)
                                    FROM             CusotmerOrder
                                    WHERE           CusID_fk = CO.CusID_fk
                        )           Total_Order_Concession,          
                        (
                                    SELECT          SUM  (Price) - SUM  (Concession)
                                    FROM             CusotmerOrder
                                    WHERE           CusID_fk = CO.CusID_fk                              
                        )           Total_Difference  
FROM             Customer C
INNER JOIN  CustomerOrder CO
ON                  C.CusID = CO.CusID_fk
                        ...... 
WHERE                       (conditions...)  
My question is what would be a better way to handle the above query?  How can i write a better yet simple query with optimized performance. I would also mention that in some of my asp.net applications, i use inline queries assigned to SqlCommand Object. The reason i mention it that since these queries are written in some class files, how would we still accomplish what i have mentioned above. Kindly could any Query Guru guide me writing better queries. I shall be obliged...

 

View 9 Replies


ADVERTISEMENT

Correlated SUB Queries?

Feb 10, 2005

I would like to combine the following 3 select statements:

1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')

2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'

3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'

the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).

the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).

The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)

Thanks a lot,

Dave Benoit

View 6 Replies View Related

SQL Server 2014 :: Memory-optimized Queries Using Table Scan Instead Of Seek?

Sep 19, 2015

I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.

Here's a partial table description:

CREATE TABLE [dbo].[Summaries_MO]
(
[SummaryId] [int] IDENTITY(1,1) NOT NULL,
[zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
[Golf] [bit] NULL,
[Homeowner] [bit] NULL,

[Code] .....

Typically, this table is accessed with a query that includes:

SELECT ...
FROM SummaryTable
WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)

This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.

As I've investigated this issue I also tried:

SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')

When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.

To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.

View 9 Replies View Related

Optimized Sp

Jul 21, 2005

Is it some how The following sp can be optimized?
IF @groupID='812846'
BEGIN
IF (SELECT count(*) from Employee where SSN= @SSN and groupID=@groupID) > 0
BEGIN
UPDATE Employee
SET NameLast=@LastName,
NameFirst=@FirstName,
NameMiddle=@MI,

WHERE SSN= @SSN and GroupId=@GroupId
select @EmpId=EmpId from Employee where SSN= @SSN and groupID=@groupID
END
ElSE
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle,SSN)
values (@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END

END

else
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle, SSN)
values
(@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END

View 8 Replies View Related

Optimized A 'LIKE' Query

Jan 14, 2007

let said, i have a table which contains a column with value such as:

,1,2,3,4,5,6,
,3,4,5,
,1,2,4,

and then i use the query:
select col1, col2 from table (nolock) where (col3 like ',1,' or col3 like ',3,')

is there any function which can be used to speed up the query or which more optimized one? Maybe something similar to IN(xxxx,xxxx,xxxx)

View 8 Replies View Related

Is This Query Optimized?

Jun 13, 2008

I started with this query:

SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BY procname,
params.parameter_id

Now, all I need from it is the column params.is_output.

I have modified it down to what I need, but I'm wondering if I can remove some of the joins or anything else for better performance without losing the proper results:

SELECT params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'

View 2 Replies View Related

Looking For A More Optimized Code

Feb 21, 2007

is there a better code for this..?

SELECT phase, stat, subject, CASE WHEN phase = 'Initial/Data Collection' THEN '1'
WHEN phase = 'Screening' THEN '2'
WHEN phase = 'Assessment and Selection' THEN '3'
WHEN phase = 'Placement' THEN 4 END AS PhaseSort

FROM (SELECT subject, stat, CASE WHEN stat = 'Application Received' THEN 'Initial/Data Collection'
WHEN stat = 'Shortlisted' OR
stat = 'For Screening' THEN 'Screening'
WHEN stat = 'For Assessment' OR
stat = 'Passed Initial Evaluation' OR
stat = 'Passed Profiles Exam' OR
stat = 'Passed Technical Exam' THEN 'Assessment and Selection'
WHEN stat = 'For Placement' THEN 'Placement' END AS phase

FROM (SELECT subject, CASE WHEN subject = 'Process Application' OR
subject = 'Application Received' THEN 'Application Received'
WHEN subject = 'Screen Application' THEN 'For Screening'
WHEN subject = 'Phone interview' THEN 'Shortlisted'
WHEN subject = 'Initial Interview' THEN 'For Assessment'
WHEN subject = 'Profiles assessment'THEN 'Passed Initial Evaluation'
WHEN subject = 'Technical Exam and Interview' THEN 'Passed Profiles exam'
WHEN subject = 'background and reference check' THEN 'Passed Technical Exam'
WHEN subject = 'Job Offer' OR
subject = 'Contract Signing' THEN 'For Placement' END AS stat

FROM dbo.filteredtask
WHERE (subject = 'application received') OR
(subject = 'process application') OR
(subject = 'screen application') OR
(subject = 'initial interview') OR
(subject = 'profiles assessment') OR
(subject = 'technical exam and interview') OR
subject = 'background and reference check' OR
subject = 'phone interview' OR
subject = 'shortlisted' OR
subject = 'For Placement' OR
subject = 'job offer' OR
subject = 'contract signing') Phases) stats
ORDER BY phasesort

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

View 6 Replies View Related

UPDATE Optimized

Feb 13, 2008

Any idea how I could do this efficiently?

For example, the SiteName & SLAClass field using select statements each time may bog down the system.

Also, I’d like to feed the CustID and Subject fields from another table call Profile instead of typing the CustID field each time.

The result of this statement is to search for customers in the subject line and if customer is found then add the customer information into the Detail table. The Profile table contains all customer information.



UPDATE [TEST3].[dbo].[Detail]
SET [CustID] = 'Book Fairs' /*fill in with field from the Profile table automatically*/
,[SiteName] = (SELECT distinct([Profile].[SiteName] )
FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail]
WHERE [Profile].[CustID] = [Detail].[CustID])
,[SLAClass] = (SELECT distinct([Profile].[SLAClass])
FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail]
WHERE [Profile].[CustID] = [Detail].[CustID])
WHERE [Detail].[CallID] IN
(SELECT [CallLog].[CallID] FROM [TEST3].[dbo].[CallLog], [TEST3].[dbo].[Subset], [TEST3].[dbo].[Asgnmnt]
WHERE [CallLog].[CallType] = 'DREAM' AND
[CallLog].[Subject] LIKE '%Book Fairs%' ) /*fill in with field from the Profile table automatically*/

View 6 Replies View Related

Table Not Optimized Or What ?

Jul 20, 2005

I have two tables in SQL 6.5 database with identical fields and indexes. Onecontains the data of August 2003 and other July 2003. Now the august tableis larger ( about 40000 more rows ) than the july table but i've noticedthat the same queries perform much faster on the august table than the julytable. Ive tried this with many different queries so i'm wondering whats thereason behind this. Is there a way to optimize a table? Remember , I'm usingSQL 6.5thx

View 4 Replies View Related

Can This Query Be Optimized?

Nov 28, 2007

Hello All,

I have this query that is taking more than 5 minutes to run, granted it involves 7 tables, 4 of which have over 100000+ rows, but there must be a quicker way of executing this.






Code Block

SELECT
ACP.COMPANY_NAME,
WOD.WO ,
WOH.SCHEDULED_DATE ,
WOH.JOB_ADDRESS_1,
WOH.JOB_ADDRESS_2,
WOH.CUSTOMER_CODE,
ARC.CUSTOMER_NAME,
ARC.BILL_TO_CUSTOMER_CODE,
APS.SUPPLIER_NAME,
APC.INVOICE_NUMBER as AP_INVOICE_NUMBER,
APC.INVOICE_DATE as AP_INVOICE_DATE,
APC.DATE_OF_RECORD as AP_DATE_OF_RECORD,
WOD.AMOUNT,
APC.CHEQUE_NUMBER,
WOH.INVOICE_NUMBER as AR_INVOICE_NUMBER,
ARI.DATE_OF_RECORD as AR_DATE_OF_RECORD
FROM
WO_WODDescription_tbl AS WOD
LEFT OUTER JOIN WO_Headers_tbl AS WOH ON WOD.COMPANY_CODE = WOH.COMPANY_CODE AND WOD.WO = WOH.WORK_ORDER_NUMBER
LEFT OUTER JOIN AP_CurrentDetails_tbl as APC ON WOD.COMPANY_CODE = APC.COMPANY_CODE AND WOD.DRILL_DOWN_NUMBER = APC.DRILL_DOWN AND WOD.AUDIT_NUMBER = APC.AUDIT_NUMBER
LEFT OUTER JOIN AR_CustomerMaster_tbl as ARC ON WOD.COMPANY_CODE = ARC.COMPANY_CODE AND WOH.CUSTOMER_CODE = ARC.CUSTOMER_CODE
LEFT OUTER JOIN AP_Suppliers_tbl as APS ON APC.COMPANY_CODE = APS.COMPANY AND APC.SUPPLIER_CODE = APS.SUPPLIER_CODE
LEFT OUTER JOIN ADM_CompanyProfile_tbl as ACP ON WOD.COMPANY_CODE = ACP.COMPANY_CODE
LEFT OUTER JOIN AR_InvoiceDetailCurrent_tbl as ARI ON WOD.COMPANY_CODE = ARI.COMPANY_CODE AND WOH.INVOICE_NUMBER = ARI.INVOICE_NUMBER
WHERE
(WOD.COMPANY_CODE = '01' OR WOD.COMPANY_CODE = '03')
AND APC.CHEQUE_NUMBER <> 'X%'
AND (APC.DATE_OF_RECORD < '20061101' AND ARI.DATE_OF_RECORD > '20061031')
ORDER BY WOD.COMPANY_CODE, WOD.WO

Can anyone give me any suggestions of how I could speed this up?
Also, I have noticed that sqlservr.exe is using more than 1.5GB of the 2GB in the machine while doing conversions from flat files to the database while the CPU is under 3% load, is this action typical of MSSQL2005?

Any help with this would be greatly appreciated.

View 3 Replies View Related

How To Get Optimized Join

Sep 30, 2006

Hi Experts,




I have following doubts on join condition

Table 1 primary key (id,sub)



Name id sub marks



xxx 61 maths 45



xxx 61 science 50







another table primary key ( id,language)



id language write



61 english yes



61 Hindi no



Output:



Xxx 61 maths 45 english yes



Xxx 61 maths 45 Hindi no



Xxx 61 science 50 english yes



Xxx 61 science 50 hindi no



how to join these tables to get



every information in 2 rows will it possible



xxx 61 maths 45 English yes



xxx 61 science 50 hindi no



please suggest me to right path

thanking u



please mail to me:nallisalmon@yahoo.co.in










View 1 Replies View Related

Correlated SQL Help

Jan 21, 2008

I've set up a correlated SQL query but i'm not entirely sure i've got it right.
The purpose of it is to get all the unapproved hours for a TimeCode where the CodeOwner matches @UserName.
It gets the unapproved hours for every code rather than the ones for the UserName.
 SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved,

(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner

FROM lward.tblHours

WHERE Approved is NULL The Username in tblHours is used for storing who is logging some hours. CodeOwner is the Owner of a paticular time code.tblHoursID, TimeCode, Date, Hours, Username, Comments, ApprovedtblCodesCodeID, CodeName, CodeDescription, CodeOwner, CodeCategoryThe only relationship is CodeID and TimeCode, CodeID is the primary key and TimeCode is the foreign.

View 7 Replies View Related

Stored Procedure Not Optimized

Feb 1, 2005

Hi ,
I created a page that list the total of hours, lunch time and expenses for the employees of the company.
I am trying to optimize this stored procedure , but it still takes more than 40 seconds for 50 employees.
select @StartDate As DateLigne, TPerson.Name, TPerson.idperson,
(select sum(coalesce(hours,0) - coalesce(lunch,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As hours,
(select sum(coalesce(nonbillable,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As nonbillable,
(select sum((coalesce(miles,0)*@mil)+ coalesce(perdiem,0)+coalesce(supplies,0)+coalesce(airfare,0)+ coalesce( gas,0) + coalesce(autorental,0)+ coalesce(other,0) ) FROM ThereportWHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As Expenses
FROM TUserProject, TPerson
WHERE TUserProject.etridperson=TPerson.idperson AND etridproject =89

Do you have any idea of how I could optimize this stored procedure?

Thanks

View 8 Replies View Related

Correlated Subquery

Jun 6, 2008

 HiI was trying an example of subquery Create Table #Temp(    PK_ID int identity(1,1),    sName Varchar(50) )Create Table #TempAddress(    PK_ID int identity(1,1),    PersonID int,    Address Varchar(100))Insert Into #Temp Values('Karan')Insert Into #Temp Values('Gupta')Insert Into #Temp Values('Karan')Insert Into #Temp Values('Karan')Insert Into #TempAddress Values(1,'Address1')Insert Into #TempAddress Values(2,'Address2')Insert Into #TempAddress Values(3,'Address3')select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)Drop Table #TempDrop Table #TempAddressBut I am getting an errorMsg 4145, Level 15, State 1, Line 29An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'. Am i doing something wrong.Kindly adviceRegardsKaran  

View 1 Replies View Related

Correlated Subquery

Apr 28, 2000

Hello,

I need to come up with a query (I really don't want to use cursors) that will perform the following:

I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.

Here are the pertinent fields: userid,message_id,message_date

I really appreciate your help,Rob

View 2 Replies View Related

Correlated Subquery

Nov 8, 2007

This really a question of approach more than anything else.

The situation is as follows:

I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.

So that...Given the date range 11/1/2007 to 11/2/2007 and the data set

Code:


11/1/2007 CompanyA req1 req2 req3 req4
11/2/2007 CompanyA req1 req2 null req4
11/1/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyC req1 null req3 req4



Only the two rows for CompanyB would ultimately be delivered.

On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?

View 1 Replies View Related

Correlated Query

Nov 10, 2006

Ajaya writes "SERIAL DATE STATUS

123 10/02/06 PENDING
123 01/06/06 DECLINE >

345 08/30/06 APPROVE>
345 10/05/06 APPROVE

567 08/14/06 APPROVE>
567 08/22/06 APPROVE

678 10/17/06 OTHER STATUS
678 10/17/06 APPROVE>
678 10/24/06 APPROVE

789 10/04/06 DECLINE
789 10/06/06 OTHER STATUS
789 10/06/06 APPROVE>

I am looking for a sql which extract
EARLIEST DATE IF STATUS = APPROVE OR DECLINE, ONLY NEED TO CONSIDER THESE TWO STATUSES.

for example serial = 789, I want to select the last row, since it is approved status.

The arrow in the right side , means i want to select that row from my sql.
If both statuses(decline and approve) is falls for a serial number, then I want to select the row with approved status with earliest date. example sir = 789

Please help"

View 1 Replies View Related

Correlated Subquery

Mar 24, 2007

Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.

My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.

View 8 Replies View Related

Correlated Update

Dec 17, 2007

I am trying to perform an update based on the following query

select o.order_id,
from orders o, accounts a
where a.account_id=r.account_id and
a.event_type IN (2003, 3057, 4003, 7005)
order by o.order_id desc

I have tried several combinations but to no avail.

Can anybody provide a solution?

Thanks in advance

Declan

View 11 Replies View Related

Correlated Subquery Help!

Mar 24, 2008

I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!

select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1

select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value

View 2 Replies View Related

Correlated Sub Query

Dec 27, 2007

I'm using Transact SQL for SQL 2000 and I'm having difficulties with a correlated sub-query.

I have a table called 'Results' like this...

Company_ID Product Spend Flag_Top25
1 Product A $100
1 Product B $250
1 Product C $450

I want to create a flag to identify if a companies spending for each product is within the Top 25% of spending within the product category across all products.

For example, the code below would identify the Top 25% of Companies with spending on Product A.

SELECT TOP 25 PERCENT Company_ID
FROM Results
WHERE Product = 'Product A'
ORDER BY Spend DESC

I'm trying the following correlated sub-query to get this done and it is not working.

UPDATE Results A
SET A.Flag_Top25 = 'Top 25% Customer'
WHERE A.Company_ID in (
SELECT TOP 25 Percent B.Company_ID
FROM Results B
WHERE B.Spend > 0
and B.Product = A.Product
ORDER BY B.Spend DESC
)

View 2 Replies View Related

Correlated Subquery Help!

Mar 24, 2008

I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!

select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1

select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value

View 3 Replies View Related

Optimized Way To Store Hashed Values?

Jul 12, 2007

What is the most optimized way to store Hashed values (obtained by Object.GetHashCode() method) in SQL Server?

Thanks.

View 4 Replies View Related

Will This Query Be Optimized For A Partitioned View?

Jul 20, 2005

Hello :-)My question is: If I query a partitioned view, but don't know the valuesin the "where x in(<expression>)" clause, i.e.: select * from viewAwhere intVal in(select intVal from tbl1) . Compared to: select * fromviewA where intVal in(5,6).Of course "intVal" is partitioning column.Will this result in an optimized query that searches only the relevanttables?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Optimized SQL Server Disk Configuration

Jan 18, 2008

I have a question concerning where to put certain database files for the followinig RAID configurations. The server has 2 RAID configs: 2 hds in a RAID 1 and 4 hds in a RAID 10. The server will host 4 database instances: A replicated db, a Reporting Services db (which technically constitutes 2 db instances) and an application db.
In order to get the best performance, should I put the OS, SQL binary and log files on the RAID 1 config with the data and tempdb on the RAID 10? If not, please explain the best solution. Thank you!

View 3 Replies View Related

Correlated And Aggregate Sql Query

Aug 8, 2007

I have a table similar to the following:
   ID   ¦   Name ID   ¦   Period From   ¦   Period To   ¦   Percentage   ¦
---------------------------------------------------------------------------
Important - Each person can have more than one entry.
What I am trying to do is get the last percentage that each person obtained.
The only way I have been able to do this is by the following:
SELECT * FROM myTable
LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1
ON myTable.NameID = t1.NameID
WHERE myTable.PeriodTo = t1.PeriodTo
 
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip

View 8 Replies View Related

Correlated Subquery In SQL Server

Mar 14, 2002

Hi, the following query works in Oracle, how do I do it in SQL Server? Thanks.


UPDATE table1 a SET a.newid =
(SELECT b.newid
FROM table2 b
WHERE a.id = b.id)


Basically, if table 1 and 2 have the same value in the "id" column, then I update the "newid" column in table a to match that of "newid" in table b.

View 1 Replies View Related

Correlated Subquery From Two Tables

Jan 19, 2014

I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.

Two quick questions:

1. Is the below considered a correlated subquery?
2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.

USE MyGuitarShop
SELECT EmailAddress,
(SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder,
(SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID
FROM Customers
GROUP BY Customers.EmailAddress

View 10 Replies View Related

Multicolumn Correlated Subquery?

May 10, 2006

Hi,I have a history table with about 400 million rows, with a uniquecomposite nonclustered index on two columns (object id and time period)which is used for most of the queries into the table, and an identitycolumn for the clustered primary key.Many of my queries use correlated subqueries to pull unique historyrows from the history table for each of a set of objects from theobject table, for instance, pulling the earliest history row for eachobject in a set. These correlated subqueries reference the object tableand return the primary key of the history table, e.g.:select *from lp_object linner join lp_object_history hon h.lp_object_id = l.lp_idwhere l.lp_set_id = 'SOME_LITERAL'and h.lp_id = (select top 1 lp_idfrom lp_object_historywhere lp_object_id = l.lp_idand lp_some_column > 0order by lp_time_period)Now, if lp_some_column is not indexed, this query has no choice but toread the entirety of every single history row for every object in theset where lp_set_id = 'SOME_LITERAL', so that it can determine iflp_some_column > 0, and because the history table is clustered by theidentity column rather than the ID of the relevant object whose historywe're tracking, the reads take forever - they have to bop all aroundthe disk. The sets I deal with tend to have about 5K objects in themand about 200K associated history rows.I'm considering reclustering by the (object id, time period) index, butthen my queries will need an extra bookmark lookup step to get the rowdata from the identity value returned by the correlated subquery. Ithink it will still be faster, though, so I will probably build a copyof the table with the alternative clustering scheme to run someperformance tests.What I'm wondering is, if I were to dispense with the identity columnaltogether and replace it with a composite primary key of (object id,time period), would I be still be able to use my correlated subqueries?Because then there wouldn't be a single column that uniquely identifieseach row in the history table and I don't think SQL Server supportsmulticolumn correlated subqueries.Thanks for reading,Seth

View 4 Replies View Related

Correlated Subquery Efficiency

Jul 20, 2005

Hello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason

View 1 Replies View Related

Query Against Partitioned View Is Not Optimized Due To CONVERT_IMPLICIT

Nov 2, 2007

We have a situation where queries against a partitioned view ignore a suitable index and perform a table scan (against 200+MB of data), where the same query on the underlying table(s) results in a 4 page index seek. I can€™t find any mention of the situation, so I€™m trying a post here.


We€™re running SQL Server 2005 Enterprise edition sp2 on Windows 2003 Enterprise Edition sp1 on a two node cluster, and it also occurs on a stand-alone development box with Developer edition. We have four tables, named Options#0, Options#1, Options#2, and Options#3. All are almost identical (script generated by SSMS and edited down a bit):


SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Options#0](
[ControlID] [tinyint] NOT NULL CONSTRAINT [DF_Options#0__ControlID] DEFAULT ((0)),
[ModelCode] [char](8) NOT NULL,
[EquipmentID] [int] NOT NULL,
[AdjustmentContextID] [int] NOT NULL,
[EquipmentCode] [char](2) NOT NULL,
[EquipmentTypeCode] [char](1) NOT NULL,
[Description] [varchar](50) NOT NULL,
[DisplayOrder] [smallint] NOT NULL,
[IsStandard] [bit] NOT NULL,
[Priority] [tinyint] NOT NULL,
[Status] [bit] NOT NULL,
[Adjustment] [int] NOT NULL,
CONSTRAINT [PK_Options#0] PRIMARY KEY CLUSTERED
(
[ModelCode] ASC,
[EquipmentID] ASC,
[AdjustmentContextID] ASC,
[ControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[Options#0] WITH CHECK ADD CONSTRAINT [CK_Options#0__ControlID] CHECK (([ControlID]=(0)))


ALTER TABLE [dbo].[Options#0] CHECK CONSTRAINT [CK_Options#0__ControlID]


The only differences between the tables are in the names and in the value defaulted to and CHECKed, which matches the table name (to support the partitioned view, of course).


We receive and load data ever week and every two month, and use an unlikely algorithm to load and manage its availability by running an ATLER on the view (to maintain the access rights defined for the hosting environment). Scripted out via SSMS, the view looks like:


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[Options] AS select * from Options#1 union all select * from Options#3


The problem is that when we issue a query like


SELECT count(*)
from Options
where ControlID = 1
and ModelCode = '2004NIC9'


The resulting query (as checked via the query plan and SET STATISTICS IO on) will get €œpartitioned€?, running against the proper table, but it will ignore the query, perform a table scan, and churn through 200+MB of data. A Similar query run against the underlying table


SELECT count(*)
from Options#1
where ControlID = 1
and ModelCode = '2004NIC9'


(with or without the ControlID = 1 clause) will perform a Clustered Index Seek and read maybe 4 pages.


Analyzing the execution plan shows that the table query work like you€™d think, but for the query against the view we get a Clustered Index Scan, with predicate:


[DBName].[dbo].[Options#1].[ControlID]=(1) AND CONVERT_IMPLICIT(char(8),[ DBName].[dbo].[Options#1].[ModelCode],0)=€™2004NIC9€™


I get the same results when explicitly listing all columns in the view. The code page on the view and tables is the same (as determined by checking properties via SSMS).


Why is the table data column being implicitly converted to the data type that it already is? Why does this occur when working with the partitioned view but not with the actual table? Can this behavior be controlled or modified without losing the (incredibly useful) data loading management benefits of the partitioned view? I€™m guessing (and hoping) it€™s some subtle quirk or mis-setting, please set me on the right path!


Philip Kelley

View 7 Replies View Related

Trouble With Correlated Subquery, Example Using Northwind

Aug 13, 2005

Suppose I have this query, which shows each order and the price of the most expensive item in each order:

SELECT Ord.OrderID, Ord.OrderDate,     MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet     INNER JOIN     Northwind.dbo.Orders AS Ord    ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate

I need to also show the ProductID that has MaxUnitPrice from the Order
Details.  I can't just add ProductID to the select list because
I'd have to group by it, and then I'd get a row for each product,
instead of a row for each order... I think I need a correlated subquery
but can't work out how to do it!

View 1 Replies View Related

Tough Correlated Subquery Issue

Aug 29, 2006

I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance

View 5 Replies View Related







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