Solved

SQL Server Query Help

Posted on 2011-02-16
7
292 Views
Last Modified: 2012-08-13
Hi Experts,

I have a customersales table and I am using the below query to find out the maximum salesid for each customer.

SELECT MAX(SaleID),CustomerID
FROM CustomerSales
GROUP BY CustomerID

It takes a very high cpu and time. CustomerSales table has 4 million records.

Can you please guide me if there a better and efficient way to write this query.

Thanks,
0
Comment
Question by:rocky_lotus_newbie
7 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 125 total points
ID: 34910778
Do you have SaleID and CustomerID fileds indexed
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 34910781
The query is as good as it gets.
However, you will find it runs MUCH faster, if you add an index on customerid, saleid

e.g.

create index ix_customersales_cs on CustomerSales (customerid, saleid)
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 34910784
Not separately, mind you, but one index involving both fields.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Assisted Solution

by:spmamidi
spmamidi earned 125 total points
ID: 34910862


I hope you would be having Clustered Index if not there use the following query

CREATE UNIQUE CLUSTERED INDEX Sales_ID_Index ON CustomerSales (CustomerSalesID);
GO

Next, build three similar nonclustered indexes:
-- The first index is on a single, nonunique column
CREATE INDEX SalesOrderID_Index1 ON CustomerSales(SaleID);
GO
-- The second index is composite and explicitly adds the clustered key column
CREATE  INDEX SalesOrderID_Index2 ON CustomerSales(SaleID, CustomerID);
GO
-- The third index adds the clustered index key as an INCLUDE column
CREATE  INDEX Sales_DetailID_Index3 ON CustomerSales(SaleID);
      INCLUDE (CustomerID);
GO

Hopefully you would get good performance results.

Shiva Mamidi



Shiva Mamidi
0
 
LVL 4

Author Comment

by:rocky_lotus_newbie
ID: 34912061
Thanks for the Inputs, I will try these indexes and see if it performs better.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34912892
Shiva - you want to create 3 indexes ALL on the same [first] column on a SINGLE table??
0
 
LVL 1

Expert Comment

by:spmamidi
ID: 34916172

Yes - There is a typo for Creating third Index.

Here is the correct version - CREATE  INDEX Sales_OrderId_Index3 ON CustomerSales(SaleID) INCLUDE (CustomerID);

Shiva Mamidi
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now