• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

SQL Server Query Help

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
rocky_lotus_newbie
Asked:
rocky_lotus_newbie
4 Solutions
 
Ephraim WangoyaCommented:
Do you have SaleID and CustomerID fileds indexed
0
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
Not separately, mind you, but one index involving both fields.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
spmamidiCommented:


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
 
rocky_lotus_newbieAuthor Commented:
Thanks for the Inputs, I will try these indexes and see if it performs better.
0
 
cyberkiwiCommented:
Shiva - you want to create 3 indexes ALL on the same [first] column on a SINGLE table??
0
 
spmamidiCommented:

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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now