Optimization of the following query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Optimization of the following query

Author

Topic

youngb912
Starting Member

14 Posts

Posted - 2010-09-13 : 10:45:24

sql = "Select * From prices"
sql &= "Where CLEM = "
sql &= AAQryStr(CLEM)
sql &= " And Source = "
sql &= AAQryStr(Source)
sql &= " And PriID = "
sql &= AAQryStr(PriID)

I need suggestion to optimize the above query? This involves NOT rewriting the query but perhaps adding an index, refreshing stats, etc that might be causing slowness in this.

Thanks

CSears
Starting Member

39 Posts

Posted - 2010-09-13 : 10:52:10

This really depends on what the data looks like.

What is the:
Total Row Count?
number of different CLEM values?
number of different Source values?
number of different PriID values?

Bear in mind that any answer we could provide would speed up this query, but may slow down other queries because we don't see the full picture of your database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-13 : 10:55:47

Please post table and index definitions. Is that SELECT * really necessary?

--
Gail Shaw
SQL Server MVP

youngb912
Starting Member

14 Posts

Posted - 2010-09-13 : 11:28:30

@CSears
Total Row Count?
Answer =3403297

Trying to gather the rest.
Thanks

@GilaMonster

INDEXES
-------------
AA_Prices clustered, unique, primary key located on PRIMARY GOPN, Source, PriID
GOPNIndex nonclustered located on PRIMARY GOPN
Prices_RecordNo nonclustered, unique, unique key located on PRIMARY RecordNo
PriIDIndex nonclustered located on PRIMARY Source, PriID

TABLE DEFINITION
-----------------
CREATE TABLE [dbo].[Prices](
[GOPN] [varchar](7) NOT NULL,
[Source] [varchar](1) NOT NULL,
[PriID] [varchar](4) NOT NULL,
[PriCode] [varchar](5) NOT NULL,
[PriName] [varchar](50) NOT NULL,
[PriStart] [datetime] NOT NULL,
[PriEnd] [datetime] NOT NULL,
[CatPrice] [money] NOT NULL,
[CatUM] [varchar](2) NOT NULL,
[CatPage] [varchar](4) NOT NULL,
[SellMin] [int] NOT NULL,
[Sell1] [money] NOT NULL,
[Sell2] [money] NOT NULL,
[Sell3] [money] NOT NULL,
[Sell4] [money] NOT NULL,
[Sell5] [money] NOT NULL,
[SellQty1] [int] NOT NULL,
[SellQty2] [int] NOT NULL,
[SellQty3] [int] NOT NULL,
[SellQty4] [int] NOT NULL,
[SellQty5] [int] NOT NULL,
[SellPrice1] [money] NOT NULL,
[SellPrice2] [money] NOT NULL,
[SellPrice3] [money] NOT NULL,
[SellPrice4] [money] NOT NULL,
[SellPrice5] [money] NOT NULL,
[NetMin] [int] NOT NULL,
[Net1] [money] NOT NULL,
[Net2] [money] NOT NULL,
[Net3] [money] NOT NULL,
[Net4] [money] NOT NULL,
[Net5] [money] NOT NULL,
[NetQty1] [int] NOT NULL,
[NetQty2] [int] NOT NULL,
[NetQty3] [int] NOT NULL,
[NetQty4] [int] NOT NULL,
[NetQty5] [int] NOT NULL,
[NetCost1] [money] NOT NULL,
[NetCost2] [money] NOT NULL,
[NetCost3] [money] NOT NULL,
[NetCost4] [money] NOT NULL,
[NetCost5] [money] NOT NULL,
[NetFirm] [varchar](1) NOT NULL,
[NetNet] [varchar](1) NOT NULL,
[RecordDate] [datetime] NOT NULL,
[RecordNo] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ChangeDate] [datetime] NOT NULL,
[Changed] [text] NOT NULL,
CONSTRAINT [AA_Prices] PRIMARY KEY CLUSTERED
(
[GOPN] ASC,
[Source] ASC,
[PriID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [Prices_RecordNo] UNIQUE NONCLUSTERED
(
[RecordNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Thanks for the help so far.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-13 : 11:50:44

Is that select * really necessary. Do you actually need all the columns?

--
Gail Shaw
SQL Server MVP

youngb912
Starting Member

14 Posts

Posted - 2010-09-13 : 11:54:42

Yes

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 13:04:19

Where is the CLEM column? It's not in your DDL but is in your query.

youngb912
Starting Member

14 Posts

Posted - 2010-09-13 : 13:20:41

Sorry........CLEM is the same as GOPN. Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 13:41:29

Since your clustered index contains those 3 columns already, you should be getting a clustered index seek now. Can you confirm that?

You can try running UPDATE STATISTICS Prices WITH FULLSCAN and see if that improves anything.

youngb912
Starting Member

14 Posts

Posted - 2010-09-13 : 15:25:34

"Since your clustered index contains those 3 columns already, you should be getting a clustered index seek now. Can you confirm that?"

Please how do I know if I'm getting a clustered index seek?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-13 : 16:08:02

Construct a SQL statement with some placeholder data for the variables, like so:

Select * From prices Where GOPN = 'ABCDEFG' And Source = 'H' And PriID = 'WXYZ'

Change the values in single quotes to match data you know is in the Prices table.

Connect to your SQL Server using Management Studio or Query Analyzer. Under the Query menu is an option to "Include Actual Execution Plan", choose that option. Run your query and examine the information displayed on the Execution Plan tab.

Look in SQL Server Books Online for more details under "execution plans".

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-13 : 16:33:52

quote:

sql = "Select * From prices"
sql &= "Where CLEM = "
sql &= AAQryStr(CLEM)
sql &= " And Source = "
sql &= AAQryStr(Source)
sql &= " And PriID = "
sql &= AAQryStr(PriID)

Could you provide the real query?

I just want to see the real query to find out any index that may be involved in execution. I will not rewrite the query, I promise

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 03:38:14

I would rewrite the query. I mention this in case my proposed rewrite is within the bounds of what you can change. If not then as-you-were!


sql = "exec sp_ExecuteSQL(N'Select * From prices "
sql &= "Where CLEM = @CLEM"
sql &= " And Source = @SOURCE"
sql &= " And PriID = @PRIID',"
sql &= "N'@CLEM varchar(7), @SOURCE varchar(1), @PRIID varchar(4)'"
sql &= ", @CLEM = " & AAQryStr(CLEM)
sql &= ", @SOURCE = " & AAQryStr(Source)
sql &= ", @PRIID = " & AAQryStr(PriID)

this would provide you with a cached query plan which could increase the performance significantly (2 to 4 fold improvement is likely, 10 fold improvement is possible assuming query with complex WHERE clause where irrelevant parts are omitted in construction of the WHERE clause - not the example you have given, but may exist in other parts of your APP)

"Is that select * really necessary. Do you actually need all the columns?
Yes"

Dangerous strategy IMHO. If someone adds a massive Text column to PRICES table in the future all SELECT * queries will retrieve it, even though it will not be needed by that process. (May be deemed impossible for this table/process, but the general principle holds; I have seen systems brought to their knees by use of SELECT * when subsequent database schema changes are made, and thus at the very least it is poor-practice.) Even if you need all the columns name them explicitly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources