Select Count slowly-CodePudding

Everybody is good, I use the following SQL statement

SELECT Count (*) FROM [tblHitRecordsDtl] inner join [tblHitRecords] on
[tblHitRecords]. Id=[tblHitRecordsDtl] hitRecId where
[tblHitRecordsDtl]. Rank=1 and (recTime & gt;=1603419942778 and recTime & lt;=
1604419942778)

Among them, the recTime is of type Integer,
When the where condition after only rank=1, under the speed of about 500 ms, but after add recTime judgment,
It takes about 3-4 seconds...
Data table only 50000, want to board a great god how to solve? Thank you.

CodePudding user response:

First of all, of the following does not consider your hardware problems,
Slow is inner join this operation, the speed depends on what you want to join the two tables of the initial size of the,
Minimize the number of connections you two tables, where judgment first before connection, can improve the speed,
Parameter recTime, you did not indicate what is a table, I'll default to the value in table tblHitRecords,

 
SELECT 
Count (1) 
The FROM 
(SELECT * FROM [tblHitRecordsDtl] WHERE [tblHitRecordsDtl]. Rank=1) AS a t1 
INNER JOIN (SELECT * FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt; AS t2 ON t2=1604419942778). The id=t1. HitRecId 
WHERE 
[tblHitRecordsDtl]. Rank=1 
AND (recTime & gt;=1603419942778 AND recTime & lt; 
=1604419942778)

Secondly if you put the t1, t2 select * change as you need a small amount of field, can also be a certain speed queries,

The reason is that the recTime the scope retrieval, itself is slower than rank=1 this,

Then you can also consider the factor of the index, the need to retrieve fields and indexes, specific methods you can check it, I no longer here,

CodePudding user response:

Sorry where forget to delete the correction of SQL is:

 
SELECT 
Count (*) 
The FROM 
(SELECT * FROM [tblHitRecordsDtl] WHERE [tblHitRecordsDtl]. Rank=1) AS a t1 
INNER JOIN (SELECT * FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt; AS t2 ON t2=1604419942778). The id=t1. HitRecId

CodePudding user response:

Very glad to receive your reply, thank you,
I will be your statement of the second [tblHitRecordsDtl] instead to perform after t1,
The results below... Speed is still the same,
Don't know the type of recTime especially Int not DateTime "there...

P.S. the CPU is 8 core i7, and no load,

CodePudding user response:

refer to the second floor percentfl response:

sorry where forget to delete the correction of SQL is:

 
SELECT 
Count (*) 
The FROM 
(SELECT * FROM [tblHitRecordsDtl] WHERE [tblHitRecordsDtl]. Rank=1) AS a t1 
INNER JOIN (SELECT * FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt; AS t2 ON t2=1604419942778). The id=t1. HitRecId

Thank you for your careful guidance, try to use the new syntax, the time is still not small...
Because so slow, cause I doubt whether the grammar is wrong,

There is currently no index is indeed... But I want to only 50000 will need to use index is improved

CodePudding user response:

Int and dateTime field difference is not much, if conditions permit is recommended to use timestamp, the better (space as long as half a dateTime),
Can try
Processors can and should not index problem
Try to * change for a specific item

 
SELECT 
Count (1) 
The FROM 
(SELECT hitRecId FROM [tblHitRecordsDtl] WHERE [tblHitRecordsDtl]. Rank=1) AS a t1 
INNER JOIN (SELECT id FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt; AS t2 ON t2=1604419942778). The id=t1. HitRecId

Speed is also related to you a number of field data

CodePudding user response:

reference 5 floor percentfl reply:

int and dateTime field difference is not much, if possible it is recommended to use timestamp, the better (space as long as half a dateTime),
Can try
Processors can and should not index problem
Try to * change for a specific item
 
SELECT 
Count (1) 
The FROM 
(SELECT hitRecId FROM [tblHitRecordsDtl] WHERE [tblHitRecordsDtl]. Rank=1) AS a t1 
INNER JOIN (SELECT id FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt; AS t2 ON t2=1604419942778). The id=t1. HitRecId 
Speed is also and a number of field data about you

Just take this article to try again, found that rate is similar, actually no difference, my headache is killing XD
This is not the limit... The two tables about every 50000 data respectively, and since you are form, growth in the number of a day will be very substantial,

Just have to check the articles were mentioned by/date range speed range [timestamp] 6 times quicker!!
To show you share @ @
"https://kknews.cc/zh-tw/code/9zbqpjl.html

CodePudding user response:

Add: actually, I put the INNER JOIN, only for [tblHitRecords] as a query, also found the approximate time...

SELECT
Count (1)
The FROM [tblHitRecords] WHERE [tblHitRecords] recTime & gt;=1603419942778 AND [tblHitRecords] recTime & lt;=1604419942778

Have other use [EXPLAIN the QUERY PLAN] view the execution PLAN, the following chart for your reference, and thank you for your assistance,

CodePudding user response:

How long will try first single table query recTime range

CodePudding user response:

refer to the eighth floor evanweng response:

try first single table query recTime need how long range

E big hello, single table query recTime range has tried, is about 3.5 s...
Think there is little difference between time,

Thank you very much!