Using WHERE one specific column to "Select * from table" Timeout, but other columns can run normally

Question

I use a WHERE restriction for one specific Integer column to do SELECT * FROM query, but it always times out without returning any results. But when I use other columns, the query runs normally. I wonder why?

I not sure whether it is cause the specific column is Integer?

SELECT [REQUEST_ID], [SIGNUM], [MATNR], [TIMESTAMP]
FROM [TICKET_INFO]
WHERE [REQUEST_ID] = 0

This does not work and always time out, but this query works normally:

SELECT [REQUEST_ID], [SIGNUM], [MATNR], [TIMESTAMP]
FROM [TICKET_INFO]
WHERE [SIGNUM] = '#'

Maybe a large number of record and no suitable index? Try using query analyser to generate the expected execution plan. — Dale K, Apr 10 '19 at 03:09
Actually there are only about 10,000 results and it does not have any index, cause I use Columnar Storage. — Eric Gong, Apr 10 '19 at 03:13
@DaleBurrell When I use Ctrl + L to show the plan, it also times out. It always keeps executing query. — Eric Gong, Apr 10 '19 at 03:53
Don't include the plan in the query, use the "Estimated Execution Plan" function which doesn't run the query. — Dale K, Apr 10 '19 at 03:54
@DaleBurrell In SQL Server Studio 2018, Ctrl + L is the quick access to "Display Estimated Execution Plan" function. — Eric Gong, Apr 10 '19 at 03:58
Which is strange that I cannot modify this field and run ' DELETE From Table WHERE Request_ID = 0 ' query. It seems this field is Blocked — Eric Gong, Apr 10 '19 at 07:05

score 0 · Answer 1 · answered Apr 10 '19 at 03:51

The issue might be related with cached query plan which is ok for some parameter versions but not for others. You can try steps to remedy the problem. First rebuild all indexes and ensure statistics on non-indexes are up to date. Try the following:

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

If this doesn't work, then try:

Use optimize for
Use WITH(RECOMPILE)
Maybe try dynamic sql (A useful link here)

Reference here: SQL Server Query time out depending on Where Clause

Still does not work. Which is strange that I cannot modify this field and run ' DELETE From Table WHERE Request_ID = 0 ' query. It seems this field is Blocked. — Eric Gong, Apr 10 '19 at 07:04

score 0 · Answer 2 · answered Apr 10 '19 at 04:26

0

I suspect that Request_ID will be a Character datatype and you are trying to compare it against integer. Due to that, it has to cast the data for every row and do a comparison.

I would suggest you to update the query this way.

SELECT [REQUEST_ID], [SIGNUM], [MATNR], [TIMESTAMP]
FROM [TICKET_INFO]
where [REQUEST_ID] = '0'

Also, read this stackoverflow answer Casting integer to Varchar

answered Apr 10 '19 at 04:26

Venkataraman R

12,181
2
31
58

It still doesn't work, I have used '0' instead of 0 before. But I will try to change Filed ID to Varchar type. – Eric Gong Apr 10 '19 at 05:28
Which is strange that I cannot modify this field and run ' DELETE From Table WHERE Request_ID = 0 ' query. It seems this field is Blocked – Eric Gong Apr 10 '19 at 07:05

score 0 · Answer 3 · answered Apr 10 '19 at 09:34

0

After checking whether it is locked by others, all connection to this server is sleeping, which means all connections are normal. Then I re-start the server by Configuration Management and It can query normally. So the method is RESTART SERVER.

answered Apr 10 '19 at 09:34

Eric Gong

107
1
1
10

Using WHERE one specific column to "Select * from table" Timeout, but other columns can run normally

3 Answers3