-11

select * from dbo.tble select * from dbo.tble

Alexo
  • 19
  • 4
  • 5
    Whats wrong with it? what have you tried? what does your sample data look like? the list goes on but theres no info to go on... – Master Yoda Aug 24 '17 at 13:43
  • It's hard to tell just by looking at it. Tunning must be performed with the requiriments and schema in mind. To only thing I can tell so far is `a.servicecenterid > ''50''` is likely to suffer from implicit casting if that column is not a char/varchar – jean Aug 24 '17 at 13:49
  • A problem can be filtering by `CONVERT(VARCHAR(8), a.CreationDate, 112)` - try to filter by `a.CreationDate` and have indexing it - HTH ;). – shA.t Aug 24 '17 at 13:50
  • more questions to add to the Master Yoda list is, did you check execution plan? what are the suggested index's? – bruno.almeida Aug 24 '17 at 13:51
  • If it is possible change `JOIN`s to `Left join`s by adding a filter over null values of left table [ref](https://stackoverflow.com/q/2726657/4519059) -HTH ;). – shA.t Aug 24 '17 at 13:52
  • From your title alone I can only recommend that you remove this question and repost on code review: https://codereview.stackexchange.com/ preferably with a lot more detail on the actual issue including the questions asked above. I can see you are a new user so dont take it personally but this is not a suitable question for StackOverflow according to the help center guidelines https://stackoverflow.com/help/how-to-ask – Master Yoda Aug 24 '17 at 13:53
  • Well you have a column in your select that is a subquery, this if often a performance issue. You have a string splitter which can also be an issue. But without a LOT of detail all anybody can do is guess. – Sean Lange Aug 24 '17 at 13:53

1 Answers1

2

Few ideas below

  1. Remove as many left joins as possible. Use inner joins where-ever possible.

  2. Remove sub-query to get ISerialNumber. Get this later in wrapper query.

  3. Create indexes on columns in the WHERE clause if not already existing.

  4. Do not use this way of date comparison. Imagine this conversion for every row in your result set.

    CONVERT(VARCHAR(8), a.CreationDate, 112) BETWEEN (' + (@StartDate) +
') AND (' + (@EndDate) + ')

Instead, convert your @startDate and @EndDate variables to datetime and compare with a.CreationDate

  1. Calculate these before running this query into another table variables. Use those variables in the query.

    (SELECT ParamValue FROM LMG.dbo.MultiValue(''' + @JurisdictionId + ''','','',1))

SELECT ParamValue FROM LMG.dbo.MultiValue(''' + @StateOfConvictionId + ''','','',1)

  2. Check Actual execution query plan in SQL Server Management Studio. The step which is taking more cost (in percentage) will need to be taken care of.

Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
Amit Kumar Singh
  • 4,393
  • 2
  • 9
  • 22