T-SQL question

[ThomasSoerensen]

15 minutes!!!!!!

I hope your query is processing a few 10's of million rows or the server is extremely under specced the only other option is that you've written some really bad SQL

the query is the last link in a chain of 7 queries that is on top of 760000 and 12000 rows in the base tables.

And I am running the dev environment on my laptop - and I know nothing about SQL so I have most likely made the worst performing SQL possible.

when I ran it in MS Access some queries would take about an hour.

15 min is improvement

 

[mudskipper]

the query is the last link in a chain of 7 queries that is on top of 760000 and 12000 rows in the base tables.

And I am running the dev environment on my laptop - and I know nothing about SQL so I have most likely made the worst performing SQL possible.

when I ran it in MS Access some queries would take about an hour.

15 min is improvement

Have you heard of indexes??

 

[ThomasSoerensen]

Have you heard of indexes??

Not much - tell me all about it - seriously.

 

[suityou01]

Not much - tell me all about it - seriously.

You have where clauses? The fields in the where clauses if they are not indexed will run slower. Much slower.

You can run the SQL Profiler tool to give you and index tuning report.

Take an example

Table

First Name, Last Name, DOB

I have some SQL

SELECT DOB from Table WHERE FirstName = 'Smith'

With no index this has to go through every row comparing 'Smith' = Value.
With an index it scans the index, retrieves the row id and returns the row.

In a nutshell. This is a broad topic and I suggest you get reading!

 

[Spacecadet]

the query is the last link in a chain of 7 queries that is on top of 760000 and 12000 rows in the base tables.

A chain of 7 queries? As in a view, querying a view, querying a view?
How long does each of the view take to run?
The optimiser is probably giving up at some point... one way to test this is to output the views into tempory tables, index them and then query the tempory tables. You might see a significant improvement.
You can also index the views
Or you could do it properly and rewrite it as a single query. There is more than likely a lot of unnecessary processing going on with all the chained views. Plus at some point there may be a breakdown in how the SQL engine is joining data... which results in a painful row-by-row raw data scan.

And I am running the dev environment on my laptop - and I know nothing about SQL so I have most likely made the worst performing SQL possible.

As long as you have a decent amount of memory and a reasonable CPU and you aren't trying to multitask you shouldn't see too much difference between laptop and server.

when I ran it in MS Access some queries would take about an hour.

15 min is improvement

I think you've picked up some bad habits from Access

 

[ThomasSoerensen]

A chain of 7 queries? As in a view, querying a view, querying a view?
How long does each of the view take to run?
The optimiser is probably giving up at some point... one way to test this is to output the views into tempory tables, index them and then query the tempory tables. You might see a significant improvement.
You can also index the views
Or you could do it properly and rewrite it as a single query. There is more than likely a lot of unnecessary processing going on with all the chained views. Plus at some point there may be a breakdown in how the SQL engine is joining data... which results in a painful row-by-row raw data scan.



As long as you have a decent amount of memory and a reasonable CPU and you aren't trying to multitask you shouldn't see too much difference between laptop and server.




I think you've picked up some bad habits from Access

most of my queries - even the ones at the end of a 7 link chain usually take 1-5 secs - there is just a few of them that take longer.

when I have got the functionality finished I will look into performance tuning.

 

[lightng]

most of my queries - even the ones at the end of a 7 link chain usually take 1-5 secs - there is just a few of them that take longer.

when I have got the functionality finished I will look into performance tuning.

Using views to simplify big b'std queries is the way to do it in my experience. I hate huge monolithic queries. Things often go wrong and take ages to debug or spot flaws.

Like anything else, if you have a big task, split it into smaller tasks and attack those. Don't try to do the whole thing in one go.

Your approach, get it working then get it working better is a sound one, especially considering your admitted level of experience with SQL.

 

[Spacecadet]

Using views to simplify big b'std queries is the way to do it in my experience. I hate huge monolithic queries. Things often go wrong and take ages to debug or spot flaws.

A well written single query is easier to debug.
Good coding practices including formating, consistant aliasing, comments where needed and judicious use of derived tables means its very easy to comment out possible troublesome pieces of SQL and quickly trace the problem.

Having SQL buried in layers of views and/or functions makes it more difficult to trace issues.

Like anything else, if you have a big task, split it into smaller tasks and attack those. Don't try to do the whole thing in one go.

Your approach, get it working then get it working better is a sound one, especially considering your admitted level of experience with SQL.

Both very true, but there is no need to obfusticate your SQL in doing so.

 

[lightng]

Having SQL buried in layers of views and/or functions makes it more difficult to trace issues.



Both very true, but there is no need to obfusticate your SQL in doing so.

I disagree (in the nicest way of course). It would be interesting to see what others think.

 

[Spacecadet]

I disagree (in the nicest way of course). It would be interesting to see what others think.

Don't get me wrong i'm not against views, they are useful for creating a simpler data model for commonly requested data sets, which then can be incorporated into queries which saves having to join in a lot of look up tables in every single query which requires that data. E.g. creating a view which joins an invoice detail table to an items table which puts the item description and cost price into the invoice detail row.

These views should be created and treated in a similar way to the base tables. They are there as static database objects and not to be created for the purpose of making a single query "easier" when there are other methods better suited to this with out cluttering up the database with more objects.

Layering should be kept low
Query -> pre definied view -> base tables
not
Query -> view -> view -> view -> base tables


If a "view" is needed in a query then its very easy to use a derived table:

select 
         invoice.invoice_number, 
         invoice.amount, 
         invoice_lines.cnt
  from customer_invoice invoice
  inner join (select invoice_number, count(*) as cnt
                from customer_invoice_details
              group by invoice_number) invoice_lines
     on invoice.invoice_number = invoice_lines.invoice_number