Simple Queries Run Slow

Question

Simple Queries Run Slow

SKP DBA

SSC-Addicted

Points: 435
More actions
October 27, 2017 at 9:42 am

#329395

Dear Experts,
Simple queries run lot of time in millions record table table have around 374 columns please suggest optimization on table and database level because simple below query takes 25 seconds for first time please advise
Running Query takes 25 seconds for first time
====================================
select cid,cvstatus from abc_cv_bank(nolock) where na_id='395473323'

Last lines syntax For reference purpose
=====================================
CREATE TABLE ABC
[CID] [bigint] IDENTITY(1,1) NOT NULL,
365 COLUMNS and Primary key then
.............................
[CountryCodeID] [bigint] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_ABC_CV_BANK] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [HCSec]
) ON [HCSec] TEXTIMAGE_ON [HCSec]
GO
SET ANSI_PADDING OFF
GO
Please suggest recommendation this is happening to multiple tables as same structure.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Firstly, what is the reason for having the nolock on you table? Is the table's data static, are no changes are happening on it while you are running this query? If not, are you happy that you may get bad results? Secondly, what indexes do you have on your table? I suspect that you have poor (or no index) choices on it.

If you're having this problem on many tables though, I imagine you have few or no indexes on most of them; thus the problem isn't with the query but that the database has been poorly designed.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

SKP DBA - Friday, October 27, 2017 9:42 AM
Dear Experts,
Simple queries run lot of time in millions record table table have around 374 columns please suggest optimization on table and database level because simple below query takes 25 seconds for first time please advise
Running Query takes 25 seconds for first time
====================================
select cid,cvstatus from abc_cv_bank(nolock) where na_id='395473323'

Last lines syntax For reference purpose
=====================================
CREATE TABLE ABC
[CID] [bigint] IDENTITY(1,1) NOT NULL,
365 COLUMNS and Primary key then
.............................
[CountryCodeID] [bigint] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_ABC_CV_BANK] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [HCSec]
) ON [HCSec] TEXTIMAGE_ON [HCSec]
GO
SET ANSI_PADDING OFF
GO
Please suggest recommendation this is happening to multiple tables as same structure.

If you don't have an index on na_id, then the query needs to read the whole table. With 367 columns, assuming that you're using large data types (seriously a bigint for a country code? How many countries are you planning to have?) that's a whole lot of data it needs to get through.

I would suggest that you get a professional that can help you to redesign your database. An index can fix your query, but it won't fix everything.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Normalize the data structure, first. Use primary keys and enforced constraints through foreign keys. Next, put an index on the na_id column. Without it, you're scanning the entire table to retrieve however many rows are equal to '395473323'. Also, you're telling me that na_id is a string so you're passing it string values? It's a number right? Store it as a number in the database and then don't use '' quotes around the number.

This is a giant topic. I'm with Thom. There's a lot more to talk about here than simple query tuning.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Grant Fritchey - Friday, October 27, 2017 10:29 AM
Normalize the data structure, first. Use primary keys and enforced constraints through foreign keys. Next, put an index on the na_id column. Without it, you're scanning the entire table to retrieve however many rows are equal to '395473323'. Also, you're telling me that na_id is a string so you're passing it string values? It's a number right? Store it as a number in the database and then don't use '' quotes around the number.
This is a giant topic. I'm with Thom. There's a lot more to talk about here than simple query tuning.

+100
😎

Thom A SSC Guru Points: 99130 More actions · Answer 5

Luis Cazares - Friday, October 27, 2017 10:18 AM
(seriously a bigint for a country code? How many countries are you planning to have?)

Ha! I hadn't noticed that. According to a Google there's 196/195 countries in the world (depending who you ask and their opinion of Taiwan). So you could easily list every country off, with an tinyint IDENTITY(0,1) column and have room to spare! Even if Catalan splits from Spain and you recognise Taiwan, you still have 59 numbers available. Plus the column is only using 1 Byte of storage, instead of 8 bytes and leaving, roughly, 2^64 values unused. 😀

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

HappyGeek SSCoach Points: 18729 More actions · Answer 6

Grant Fritchey - Friday, October 27, 2017 10:29 AM
Normalize the data structure, first. Use primary keys and enforced constraints through foreign keys. Next, put an index on the na_id column. Without it, you're scanning the entire table to retrieve however many rows are equal to '395473323'. Also, you're telling me that na_id is a string so you're passing it string values? It's a number right? Store it as a number in the database and then don't use '' quotes around the number.
This is a giant topic. I'm with Thom. There's a lot more to talk about here than simple query tuning.

Have to agree, sounds more like an imported Excel spreadsheet than a database, the data needs normalizing with appropriate keys and indexes.

...

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

Thom A - Saturday, October 28, 2017 7:28 AM
Luis Cazares - Friday, October 27, 2017 10:18 AM
(seriously a bigint for a country code? How many countries are you planning to have?)
Ha! I hadn't noticed that. According to a Google there's 186/185 countries in the world (depending who you ask and their opinion of Taiwan). So you could easily list every country off, with an tinyint IDENTITY(0,1) column and have room to spare! Even if Catalan splits from Spain and you recognise Taiwan, you still have 69 :hehe: numbers available. Plus the column is only using 1 Byte of storage, instead of 8 bytes and leaving, roughly, 2^64 values unused. 😀

Ah, but what if we need historical records for countries. The regions & principalities that make up the Holy Roman Empire alone puts us into needing an INT. Pretty sure BIGINT is still overkill though.

And yes, kidding.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 8

SKP DBA - Friday, October 27, 2017 9:42 AM
Dear Experts,
Simple queries run lot of time in millions record table table have around 374 columns please suggest optimization on table and database level because simple below query takes 25 seconds for first time please advise
Running Query takes 25 seconds for first time
====================================
select cid,cvstatus from abc_cv_bank(nolock) where na_id='395473323'

Last lines syntax For reference purpose
=====================================
CREATE TABLE ABC
[CID] [bigint] IDENTITY(1,1) NOT NULL,
365 COLUMNS and Primary key then
.............................
[CountryCodeID] [bigint] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_ABC_CV_BANK] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [HCSec]
) ON [HCSec] TEXTIMAGE_ON [HCSec]
GO
SET ANSI_PADDING OFF
GO
Please suggest recommendation this is happening to multiple tables as same structure.

There's a really big chance that it's compile time that's taking so long. If abc_cv_bank is a view, even worse. If the code is coming from an ORM, then even worse because it will have to recompile for almost every usage thanks to the non-parameterized value in the WHERE clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)