List page statement, add quotes to the conditions and no quotes many query speed difference, a great-CodePudding

Business, there is a table in the system data quantity is 20000, ID primary key, itemid indexed,

 CREATE TABLE [dbo] [TABLE] (
[ID] [CHAR] (38) NOT NULL, 
[title] [VARCHAR] (2000) NOT NULL, 
[itemid] [BIGINT] NOT NULL, 
[column1] [BIGINT] NOT NULL, 
[column2] [BIGINT] NULL, 
[column3] [VARCHAR] (200) NOT NULL, 
[column4] [VARCHAR] (50) NULL, 
[column5] [VARCHAR] (60) NOT NULL, 
[column6] [CHAR] (36) NOT NULL, 
[status] [TINYINT] NOT NULL, 
[createtime] [SMALLDATETIME] NULL, 
[publishtime] [SMALLDATETIME] NULL, 
[parentGuid] [BIGINT] NULL)

If ITEMID without single quotes, this statement query is executed for a long time, for a long time to collapse, only 20000 data,

 select top 40 ID, column1, title, column2, column3, createTime from dbo. Table WHERE itemid=131016105515202255 and status=0 and ID 
Not in (select top 180000 ID from the Table where itemid=131016105515202255 and status=0 order by createTime desc) order by createTime desc

But after the ITEMID added a single quotes, normal, basically, a second out data,

 select top 40 ID, column1, title, column2, column3, createTime from dbo. Table WHERE itemid='131016105515202255' and status=0 and ID 
Not in (select top 180000 ID from the Table where itemid='131016105515202255' and the order status=0 by createTime desc) order by createTime desc

Clearly is BIGINT type ah, why to want to add single quotes to perform high efficiency? The younger brother database research is not deep, want to know why, please know that the great god grant instruction,

CodePudding user response:

You this way of paging was too old, only the school teacher in teaching,
You try in this way:

 - 1. Create an index 
The CREATE INDEX IX_table_createTime ON [table] (createTime) 

- 2. Convert row_number paging 
SELECT * 
The FROM (
SELECT 
ID, 
Column1, 
The title, 
Column2, 
Column3, 
CreateTime, 
ROW_NUMBER () OVER (ORDER BY createTime DESC) AS rids 
The FROM dbo. Table 
WHERE itemid=131016105515202255 
AND the STATUS=0 
) AS tt 
WHERE tt. Rid> 180000 AND tt. Rid<=180000 + 40

If you are a sqlserver2012 + new version, such as can also use the offset limit, efficiency is similar, but writing more concise,

CodePudding user response:

reference 1/f, gypsy song response:

this way paging was too old and you only school teacher teaching, in this
You try in this way:
 - 1. Create an index 
The CREATE INDEX IX_table_createTime ON [table] (createTime) 

- 2. Convert row_number paging 
SELECT * 
The FROM (
SELECT 
ID, 
Column1, 
The title, 
Column2, 
Column3, 
CreateTime, 
ROW_NUMBER () OVER (ORDER BY createTime DESC) AS rids 
The FROM dbo. Table 
WHERE itemid=131016105515202255 
AND the STATUS=0 
) AS tt 
WHERE tt. Rid> 180000 AND tt. Rid<=180000 + 40 
If you are a sqlserver2012 + new version, such as can also use the offset limit, the efficiency is similar, but writing more concise,

To learn, but we this is an old system, client is not to upgrade, architecture can not change, can only use it first

CodePudding user response:

refer to the second floor na method number freeze response:

Quote: refer to 1st floor gypsy song response:

You this way of paging was too old, only the school teacher in teaching,
You try in this way:
- 1. Create an index
The CREATE INDEX IX_table_createTime ON [table] (createTime)

- 2. Convert row_number paging
SELECT *
The FROM (
SELECT
ID,
Column1,
The title,
Column2,
Column3,
CreateTime,
ROW_NUMBER () OVER (ORDER BY createTime DESC) AS rids
The FROM dbo. Table
WHERE itemid=131016105515202255
AND the STATUS=0
) AS tt
WHERE tt. Rid> 180000 AND tt. Rid<=180000 + 40

If you are a sqlserver2012 + new version, such as can also use the offset limit, the efficiency is similar, but writing more concise,

To learn, but we this is an old system, client is not to upgrade, architecture can not change, can only use the first so the

Row_number only need 2005 or higher, it can use?