Count on table with million data too slow

Question

Post reply

Count on table with million data too slow

appdev13

Old Hand

Points: 356
More actions
March 18, 2015 at 2:58 am

#298752

I am having a table with over 120 million rows. I am executing a count query and it is taking about 4 mins to execute. Every 1 second 100 rows are being inserted into the table also with current time value in gpstime field.
indexes are created on id and gpstime fields.
select count(id)
from tablename
where gpstime between A and B
and id=123;

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Any indexes on the table?

-- edit: sorry, I was too fast, you mentioned this already in your question.

What does the query plan say? Is an index seek used?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What's the definition of the table and index (CREATE statements please)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

appdev13 Old Hand Points: 356 More actions · Answer 3

clustered index is getting used as per the query plan.

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 4

appdev13 (3/18/2015)
clustered index is getting used as per the query plan.

You really need to give more information. We cannot see your screen.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

appdev13 Old Hand Points: 356 More actions · Answer 5

Table schema is as follows:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TestTable](

[id] [bigint] IDENTITY(0,1) NOT NULL,

[sys_service_id] [bigint] NOT NULL,

[sys_msg_type] [int] NOT NULL,

[sys_proc_time] [datetime] NOT NULL,

[sys_proc_host] [varchar](45) NOT NULL,

[sys_asset_id] [varchar](45) NULL,

[sys_geofence_id] [int] NULL,

[sys_device_id] [bigint] NOT NULL,

[gps_time] [datetime] NOT NULL,

[gps_latitude] [float] NOT NULL,

[gps_longitude] [float] NOT NULL,

[gps_orientation] [float] NOT NULL,

[gps_speed] [float] NOT NULL,

[gps_fix] [int] NOT NULL,

[geo_street] [varchar](150) NULL,

[geo_town] [varchar](50) NULL,

[geo_country] [varchar](50) NULL,

[geo_postcode] [varchar](100) NULL,

[jny_distance] [float] NULL,

[jny_duration] [int] NULL,

[jny_idle_time] [int] NULL,

[jny_status] [varchar](10) NOT NULL,

[jny_leg_code] [int] NULL,

[jny_device_jny_id] [int] NULL,

[des_movement_id] [int] NULL,

[des_vehicle_id] [int] NULL,

[tel_state] [int] NOT NULL,

[tel_ignition] [bit] NULL,

[tel_alarm] [bit] NOT NULL,

[tel_panic] [bit] NOT NULL,

[tel_shield] [bit] NOT NULL,

[tel_theft_attempt] [bit] NOT NULL,

[tel_tamper] [bit] NOT NULL,

[tel_ext_alarm] [bit] NOT NULL,

[tel_journey] [bit] NOT NULL,

[tel_journey_status] [bit] NOT NULL,

[tel_idle] [bit] NOT NULL,

[tel_ex_idle] [bit] NOT NULL,

[tel_hours] [int] NULL,

[tel_input_0] [bit] NULL,

[tel_input_1] [bit] NULL,

[tel_input_2] [bit] NULL,

[tel_input_3] [bit] NULL,

[tel_temperature] [float] NULL,

[tel_voltage] [varchar](1) NULL,

[tel_odometer] [float] NULL,

[tel_poweralert] [bit] NULL,

[tel_speedalert] [bit] NULL,

[tel_boxalert] [bit] NULL,

[signature] [varchar](50) NOT NULL,

[data_status] [varchar](2) NOT NULL,

[vehicle_status] [varchar](4) NOT NULL,

[firmware_version] [varchar](255) NOT NULL,

[gps_validity] [varchar](50) NOT NULL,

[latitude_direction] [varchar](4) NOT NULL,

[longitude_direction] [varchar](4) NOT NULL,

[visible_satellite] [int] NOT NULL,

[altitude] [varchar](255) NOT NULL,

[angle] [varchar](255) NOT NULL,

[I1] [tinyint] NOT NULL,

[I2] [tinyint] NOT NULL,

[I3] [tinyint] NOT NULL,

[I4] [tinyint] NOT NULL,

[I5] [tinyint] NOT NULL,

[I6] [tinyint] NOT NULL,

[I7] [tinyint] NOT NULL,

[I8] [int] NOT NULL,

[I9] [tinyint] NULL,

[I10] [tinyint] NULL,

[I11] [tinyint] NULL,

[I13] [tinyint] NULL,

[I12] [tinyint] NULL,

[I14] [tinyint] NULL,

[I15] [tinyint] NULL,

[I16] [tinyint] NULL,

[I17] [tinyint] NULL,

[I18] [tinyint] NULL,

[tel_fuel] [varchar](50) NOT NULL,

[tel_fuel2] [varchar](255) NULL,

[tank_capacity] [varchar](50) NOT NULL,

[battery_voltage] [varchar](255) NOT NULL,

[signal_strength] [varchar](255) NOT NULL,

[mobile_country_code] [varchar](50) NOT NULL,

[mobile_network_code] [varchar](50) NOT NULL,

[location_area_code] [varchar](50) NOT NULL,

[cell_id] [varchar](255) NOT NULL,

[checksum] [varchar](255) NOT NULL,

[meterstatus] [char](1) NULL,

[tripno] [varchar](25) NULL,

[panicstatus] [char](1) NULL,

[IP1] [char](1) NULL,

[BatteryStatus] [char](1) NULL,

[IP2] [char](1) NULL,

[IP3] [char](1) NULL,

[tel_voltage2] [float] NULL,

[variation] [varchar](255) NULL,

[serial1_voltage] [varchar](255) NULL,

[serial2_voltage] [varchar](255) NULL,

[battery_status] [varchar](255) NULL,

[serial1_ADvalue] [int] NULL,

[serial2_ADvalue] [int] NULL,

[ServerIp] [varchar](20) NULL,

[ServerPort] [varchar](20) NULL,

[address_from_device] [varchar](500) NULL,

PRIMARY KEY CLUSTERED

(

[id] ASC,

[sys_service_id] ASC,

[gps_time] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Index definitions (CREATE INDEX) and table definition please. I think I know what's going on, but need confirmation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

appdev13 Old Hand Points: 356 More actions · Answer 7

http://www.mediafire.com/convkey/cbad/9pt99xg999borf1zg.jpg

appdev13 Old Hand Points: 356 More actions · Answer 8

appdev13

Old Hand

Points: 356

March 18, 2015 at 5:20 am

#1784724

Query execution plan

Lowell SSC Guru Points: 323518 More actions · Answer 9

there a nice softball of a suggested missing index right there.....i bet you could hit that one out of the park.

also, why not count(*)? it would be faster, i would think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

appdev13 Old Hand Points: 356 More actions · Answer 10

appdev13

Old Hand

Points: 356

March 18, 2015 at 5:29 am

#1784728

ok..thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Please?

GilaMonster (3/18/2015)
Index definitions (CREATE INDEX) and table definition please. I think I know what's going on, but need confirmation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 12

Lowell (3/18/2015)
...
there a nice softball of a suggested missing index right there.....i bet you could hit that one out of the park.
also, why not count(*)? it would be faster, i would think.

Also, the execution plan shows (partially) an index scan, while you probably want an index seek.

However, without the table DDL and the CREATE INDEX statements, there's not much we can do.

-- edit: somehow I missed the previous reply where the DDL has been posted

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Lowell (3/18/2015)
also, why not count(*)? it would be faster, i would think.

In this case it's unlikely to be faster as the column in the count is already in the where clause. Since null values can't be returned by the where clause predicate, the optimiser will likely be treating it as a count(*)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 14

GilaMonster (3/18/2015)
Lowell (3/18/2015)
also, why not count(*)? it would be faster, i would think.
In this case it's unlikely to be faster as the column in the count is already in the where clause. Since null values can't be returned by the where clause predicate, the optimiser will likely be treating it as a count(*)

that's where i'm a little weak Gail; i of course saw that the column being counted is the column being filtered int he WHERE, so i'd think nulls would be excluded, but i wasn't sure if the optimizer would shortcut the logic or not.

I know you said *likely*, but is it really a given that it would do that , assuming an index on that column?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!