3

I have some T-SQL (SQL Server 2008) that I inherited and am trying to find out why some of queries are running really slow. In the Actual Execution Plan I have three clustered index scans which are costing me 19%, 21% and 26%, so this seems to be the source of my problem.

The contents of the fields are usually numeric (but some job numbers have an alpha prefix)

The database design (vendor supplied) is pretty poor. The max length of a job number in their application is 12 chars, but in the tables that are joined it is defined as varchar(50) in some places and varchar(15) in others. My parameter is a varchar(12), but I get same thing if I change it to a varchar(50)

The node contains this:

Predicate: [Live_Costing].[dbo].[TSTrans].[JobNo] as [sts1].[JobNo]=CONVERT_IMPLICIT(varchar(50),[@JobNo],0)

sts1 is a derived table, but the table it pulls jobno from is a varchar(50)

I don't understand why it's doing an implicit conversion between 2 varchars. Is it just because they are different lengths?

I'm fairly new to the execution plan

Is there an easy way to figure out which node in the exec plan relates to which part of the query? Is the predicate, the join clause?

Regards

Mark

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mark1234
  • 1,110
  • 2
  • 24
  • 41
  • 7
    [Probably a collation mismatch](http://blogs.msdn.com/b/arvindsh/archive/2012/09/18/sql-collation-and-performance.aspx). Doesn't look like it will be the cause of your problem though as the convert happens on the parameter rather than the column. – Martin Smith Sep 26 '12 at 20:15
  • Well, to give you a square and fair answer some more information is needed. Maybe you can give us some more details on the execution plan? Maybe you can post some pictures or the xml from it somewhere? – Mark Kremers Oct 03 '12 at 13:14
  • It's a huge query. I'd post the exec plan, but there doesn't seem to eb a way to add attachments (that I can find) if I edit the post – mark1234 Oct 03 '12 at 17:25
  • When you edit your post you can add an image :) – Mark Kremers Oct 17 '12 at 09:40
  • Or, use a tool that can help you understand more of the execution plans magic: http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp – milivojeviCH Dec 05 '12 at 07:06
  • 2
    When you say "sts1" is a derived table - is this in a nested select? If so, why is the predicate on the join and not in the nested select / CTE? Seems a bit odd to ask SQL to get all the data into a derived table then ask it to perform a filter outside of the derived table (unless there are other uses for the data?) Would help to see the SQL itself. – fritterfatboy Dec 31 '12 at 13:10
  • thanks for comment. I'll have to review the code again. Been a while since I looked at it and it's inherited code. Been dragged to other projects in meantime, so will be a while before I get back to it. Yes it is a nested select. – mark1234 Jan 01 '13 at 13:44
  • I would have said that the implicit conversion happened due to the different lengths... Are you perfectly sure that you get the same implicit conversion when you change the input parameter to 50? Perhaps it's one of the other tables that you mention, if your query involves more of them, that gets into trouble and needs to be implicitly converted. Implicit conversion is no good for performance, since index seek can not be used, so you should try to get the field lengths in synch if you are joining on them. – Andreas Jansson Jan 02 '13 at 15:11

1 Answers1

1

Some variables can have collation: enter link description here

Regardless you need to verify your collations, which can be specified at server, DB, table, and column level.

First, check your collation between tempdb and the vendor supplied database. It should match. If it doesn't, it will tend to do implicit conversions.

Assuming you cannot modify the vendor supplied code base, one or more of the following should help you: 1) Predefine your temp tables and specify the same collation for the key field as in the db in use, rather than tempdb. 2) Provide collations when doing string comparisons. 3) Specify collation for key values if using "select into" with a temp table 4) Make sure your collations on your tables and columns match your database collation (VERY important if you imported only specific tables from a vendor into an existing database.)

If you can change the vendor supplied code base, I would suggest reviewing the cost for making all of your char keys the same length and NOT varchar. Varchar has an overhead of 10. The caveat is that if you create a fixed length character field not null, it will be padded to the right (unavoidable).

Ideally, you would have int keys, and only use varchar fields for user interaction/lookup:

create table Products(ProductID int not null identity(1,1) primary key clustered, ProductNumber varchar(50) not null)

alter table Products add constraint uckProducts_ProductNumber unique(ProductNumber)

Then do all joins on ProductID, rather than ProductNumber. Just filter on ProductNumber.

would be perfectly fine.

Community
  • 1
  • 1
Norman Seymore
  • 36
  • 2