Tuning required on tables

Question

Tuning required on tables

Arvind-57714

SSCrazy

Points: 2529
More actions
December 20, 2004 at 5:43 pm

#109954

People,
I have 2 tables with 4 and 9 million records resp. I have to join these tables to get some required output. This takes me a lot of time. Pls advice as to how i can improve this performance.
I have clustered and non clustered indexes on both these tables and i have created an indexed view on top of them. Any ideas welcome.
Cheers

Arvind

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

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

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

Hi Arvind

Load up Query Analyser and turn on "display estimated execution Plan" under the query window. You should then get to see what the execution engine is thinking of doing.

If you see some table scans happening there, then for some reason SQL Server is skipping your indexes (maybe they are not covering the right columns, or not giving it good enough results).

Also, try turning on the "show execution plan" which shows you what SQL Server *actually* does to execute your sql. Again, Table scans are probably bad, but have a look for the thing that is taking up the most time, this is the thing you will want to improve.

Other than that, how many rows are you returning ? And where is that output going ? If you are using Query Analyzer or Enterprise Manager to view results of more than a few thousand rows, your going to be eating up a lot of memory. I've seen a few developers return millions of rows to their desktop PC's, depleating all their available RAM, and complaining about slow performance. The Server was responding lightning fast, jsut the developers machine took forever to render the output.

Julian Kuiters
juliankuiters.id.au

Christian Kuhnlein SSC Enthusiast Points: 186 More actions · Answer 2

Hi Arvind,

in addition to the execution plan described by Julian (look for sort operations, which are often very time consuming), there is a tool called Index Tuning Wizard that can be found in the Query menu of SQL Query Analyzer. My own experiences with this tool were not great, but in a few cases it might help to find out which index to create.

And finally, sometimes the only way is to make your tables smaller (at least if you don't want to buy a new server). Try to split the tables into partitions, for example build a single table for every fiscal year of Sales data. On every table, create a check constraint on the partition column (e.g. FiscalYear = 2003 on Table A, FiscalYear = 2004 on Table B, ...). Then create a view that joins all partitions (SELECT ... FROM Table2003 UNION ALL SELECT ... FROM Table2004 ...).

This worked great in our environment. You can even improve the results if you can put the single partitions on different physical disk drives.

Regards

Christian

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Actually if you could provide DDL and the query you are more likely to receive good answers. Anything else is a shot in the dark.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

glively Mr or Mrs. 500 Points: 534 More actions · Answer 4

glively

Mr or Mrs. 500

Points: 534

December 21, 2004 at 8:18 am

#534626

Have you setup a relationship between the tables?

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 5

Julian,

I have checked the Execution plan and the estimated execution plan. The major chunk of time is taken by a Table scan on this view. It is not using my index i guess. The result is usually a recordset with maximum of 5 rows and 3 columns.

Christian,

The tables are Lookup tables there will not be any activity on the data in this for a month. I mean we will have this data refresh once a month only. And yes I have used the index tuning wizard and it does not suggest any new indexes.

Frank,

The query in the view is :

SELECT AD.Address_SK, AD.Type_Of_Address, MBD.Tel_Nbr as BTN,

isnull(AD.Street_Address1, '') + ' ' + isnull(AD.Street_Address2, '') +

' ' + isnull(AD.Street_Address3, '') + ' ' + isnull(AD.Street_Address4, '') +

' ' + isnull(AD.City, '') + ' ' + isnull(AD.State, '') + ' ' + isnull(AD.Zip_Basic, '') +

' ' + isnull(AD.Zip_Plus_4, '') AS Full_Address

FROM dbo.MAIN_BTN_DIMENSION MBD WITH (NOLOCK)

INNER JOIN dbo.ADDRESS AD WITH (NOLOCK)

ON MBD.Main_BTN_SK = AD.Main_BTN_SK

And the query in the Proc is :

Select BTN, Type_Of_Address, Full_Address

From VwAddress

Where BTN = @BTN

Any help would be appreciated.

Cheers,

Arvind

Christian Kuhnlein SSC Enthusiast Points: 186 More actions · Answer 6

Hi Arvind,

which Indexes are defined on the tables?

Does MBD.Tel_Nbr have the same format as @BTN and (sorry I'm asking, but I've seen such things before) does Main_BTN_SK have the same format in both tables?

Partitioning tables by the time dimension was only an example. You could use any other column as well if it will just give you a somewhat regular distribution (that means that not 90% of your data will end up in the same partition table).

The Index Tuning Wizard is a strange tool indeed. Seems it will only propose things if you have not defined any indexes at all or something completely weird.