- the customer table 
The create table TB_Customer 
(
ID int identity primary key, 
The Name nvarchar (10), 
Mobile nvarchar (10) 
) 
- sales of single table 
The create table TB_Sale 
(
ID int identity primary key, 
Year int - Year 
CustomerID int, - the customer table foreign keys 
IsCard bit default (0), - card sales 
IsProduct bit default (0), retail 
 -SalePrice decimal (10, 2) - sales of 
) 
- the card table 
The create table TB_Card 
(
ID int identity primary key, 
Year int - Year 
Can't Statu int, - 1, 2, 3 are available, and has been used 
CardNumber nvarchar (20), 
CardPrice int - card lines 
BuyDiscount decimal (3, 2), - buying discount 
CustomerID int, - the customer table foreign keys 
SaleID int - sales outside the single table keys 
) 

The above three tables, in which the customer table data of 10 w, 30 w, sales of single table data card table data of 500 w
Now needs to collect a few data:
1, the query all the customer's purchase and card purchase quota
2, query the sales of a collection of different states card
Above two methods, what I use is the basic way of query summary

 
- check all customer purchases and card purchase limit 
The select c.I D, citigroup ame, c. obile, 
(select ISNULL (sum (s.S alePrice), 0) from TB_Sale s where s.C ustomerID=c.I D) as' SalePrice, 
(select ISNULL (sum (case when db uyDiscount> 0, dc ardPrice * db uyDiscount then else, dc ardPrice end), 0) from TB_Card d the where, dc ustomerID=c.I d) as' CardPrice '
The from TB_Customer c 

- to track the status of sales orders in different card collection 
Select * from TB_Card where Statu=1 and SaleID=@ SaleID 
Select * from TB_Card where Statu=2 and SaleID=@ SaleID 
Select * from TB_Card where Statu=3 and SaleID=@ SaleID 

I use the method of execution is slow, I am the CustomerID TB_Sale, TB_Card Statu in the CustomerID, SaleID index,
This method performs CPU consumption, the execution time is particularly slow, how to improve the query speed and performance, need not to need to do partition

CodePudding user response:

Customer has 100000, do you want to breath out 100000 messages, even if is not associated with other tables, faster than you?

Index in screening, retain only a small number of records under the condition of effective, you all look up, what's the meaning of the index?

Suggestion:
1. In space, in time, increase the fields in the customer table, save the value of the corresponding update these values when buy, don't wait for the query to calculate slowly,
2. If it is a web site or other client access, increase the paging, dozens of each page

CodePudding user response:

With the left was on the join TB_Sale CustomerID fields indexed TB_Card CustomerID fields will also add index

CodePudding user response:

Check the collection of the three statements to build a SaleID, Statu joint index, two fields should be very fast speed,

In addition, not a return to the client so much data, using paging to return

CodePudding user response:

According to the year is the most ideal method partitions, you can refer to SQL SERVER HELP menu;
No customer hard to take a a seen hundreds of thousands of data,