Solved

Speeding up queries.

Posted on 2004-08-23
20
437 Views
Last Modified: 2012-05-05
Hi.

I'm in the process of converting a non SQL DB application into MS SQL.

I've got the data in and it is slow.

The original application only worked on 1 row at a time and had no SQL support.

My question relates, I suppose, to indexes.

The main tables currently have 250,000 header style rows with 1,500,000 transaction style rows.

The tables are linked via the record number (which was imported and is unique).

The new app is to investigate very small ranges of data and normally provide counts.

The "headers" are tickets and the "transactions" are casings. There are a maximum of 20 casings per ticket.

A casing will have a casing code, an acceptance, a rejection, a comment, a price and other things. Lookups to appropriate tables (an acceptance code will link to the acceptancecodes table, etc) also exist.

The sort of thing being asked for is the number of casings where a rejection code of ER1 exists for all tickets processed for a particular customer in a particular date range.

The customer column and date are on the ticket.

With another SQL DB I have used in the past, I can say "EXPLAIN SELECT ...". This reports the indexes being used for the statement and other things.

Is there a way of seeing HOW a query will be executed?

I don't think I want to add indexes to every column, though every column could be used as part of a query specifically to filter the results of the query.

Now here is another thing to consider.

The data, currently and for the foreseeable future, is being exported from one system and imported into MS SQL.

Once the data is in MSSQL, it is not being edited.

Most of the time, the data being requested is counts of casings for a particular condition for a particular customer.

I was considering using a data cube (now just because I used the word's "data cube", doesn't mean I understand how to use them or even if it is appropriate).

I think this can be used to deal with this sort of aggregation.

Ideas, suggestions, etc on the OLAP/Data Cube idea. How do you know when it is appropriate.

And anything else on my query analysis issue.

Thanks,

Richard Quadling.
0
Comment
Question by:Richard Quadling
  • 8
  • 5
  • 4
  • +1
20 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 400 total points
ID: 11869541
Sounds like you could use an OLAP cube to browse this data. However, it will result in a very large cube (relative to your data set) and may not give you much advantage.
The OLPA advantage, appart from flexibility will be that it can store aggregates. This is really useful when you want to aggregae large sets of data - e.g. you have 100's records per day, and you want a roll-up to the count/sum for a year, or a month. If, as you say
"The new app is to investigate very small ranges of data and normally provide counts"
then you will not really get any performance benefit with an OLAP - it will be no different to querying your database direct, and it will result in you duplicating your data etc. Also, although you do not create them namually, effectivley what will happen is that OLAP will create an index on each of your dimensions, so it is back to the same thing as doing it directly on the database.
You may be as well off creating the indexes on which ever columns you will be querying on anyway.
You can get an explain plan using Query Analyser. Paste your queries into a QA window, and simply click on the icon, or Query->display Estimated Execution Plan. QA can also suggest which indexes to create (and even create them for you if you want) based on whatever queries you use.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11869580
Excellent comments.

Thanks.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11869607
I see that there is an index tuner.

Can I build a list of SELECT statements and get it to work on them?

I think so, but not sure.

Richard.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 100 total points
ID: 11869617
Richard,

In a similar situation to this I prepared a  table for statistics (record counts and the like)  and
updated info to this after importing fresh data.

I would make sure to have a clustered index on your Key fields
and non-clustered on your search criteria.

Also, VERY important is the hardware configuration of  your server.
SQL server is VERY good up until the ammount of data in the table your are trying to analyze
exceeds the RAM memory of the server.

It sounds to me like you may be exceeding this limit.

Jay
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11869650
Yes, you can simply pase in anu number of queries into the QS window and it will analyse them all together for you
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11869722
Ok. With just my first simple query, the tuner has come up with a composite index. I was not expecting that, but I suppose it makes sense.

There is in the tuner for a column which is in another index.

The column is called ParentID (and is the link between the header and the transactions).

The existing index I have is ...

ParentID,LineNumber

where the line number is the entry on the "ticket".

I've just looked at the Workload Analysis Report.

WOW!

This is good.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11869784
Clustered and non-clustered?

The recommendations is to produce a nonclustered index (which contains a uniqueID column) for the "header" table and a clustered index for the "transaction" table. The clustered index does not include a unique column.

How do you know when to use clustered and non-clustered?

Richard.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11869835
a clustered index physically arranges the table on disk by the key you defined.

you use this when you can tell that it would be advantagous to do so.

also, you can have only ONE clustered index (obviously) ..

keys are good, but say your primary query to one table is Lastname firstname...

then you might want to consider this.  

Just keep in mind the primrary purpose for the table.

Jay
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11870097
Not obvious.

Why can't all indexes be clustered?

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11870189
A clustered index physically sorts the data in the table.So, if you have a table with two columns, firstname, lastname & you put a clustered index on lastname, then all the andersons would be at the start of the table, while all the Yeats would be at the end.
If you created a non-clustered index, the data is not touched, instead an index is created with all the surnames in aphapbetical order, and a pointer back to the table to say where the record is. As well as a clusterd index on lastname, You could create a non-clusted index on firstname, which keeps an alphabetical list of all first names, and points back to where in the table they are - so all the Bills are near the start of the index, abd all the Zachs are near the end. But the Bills could be found anywhere in the table itself, as could the Zachs. If you tried to create a clustered index on firstname, then all the Bills would be moved to the start of the table etc - and then all the Andersons couldn't be still at the start of the table... i.e. you can only physically sort the data by one thing. Now you can have a clusterd index on both columns together, so that within the andersons, all the bill andersons come before all the zach andersons etc
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11870212
Ah. Sorry. Missed the "sorts the data in the table" bit. Doh!

Well. I think my questions have been VERY well answered.

Thank you VERY much.

Expert experts.

Richard.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11876953
"also, you can have only ONE clustered index (obviously) .. "

Techincally, you could have more clustered indexes by creating indexed views and creating a clustered index there as well...
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11879931
ahh -- but that is not quite the same thing as a clustered index.
and they would only be helpfull on Enterprise edition of 2000

You can create Indexed Views on all editions of SQL Server 2000. While it is possible to create indexed views in all editions of SQL Server 2000, the benefits in performance can be obtained only in the Enterprise and Developer edition of SQL Server 2000. This is because the Query Optimizer does not take the indexes on the views into consideration when generating an execution plan in other editions.


JAY

0
 
LVL 34

Expert Comment

by:arbert
ID: 11885596
"ahh -- but that is not quite the same thing as a clustered index. "

Sure it is--the view is materialized and the table is created with a clustered index--just like a regular table...



" the benefits in performance can be obtained only in the Enterprise and Developer edition of SQL Server 2000. "

Not quite a true statement--you just have to explicitly reference the views whereas the other version will use the indexed view if it can.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11885667
Hmm .. COOL..
I'll file that away for future use.

As always Arbert your answers are very, very good ..

Jay
0
 
LVL 34

Expert Comment

by:arbert
ID: 11885749
Thanks :)  I've just learned some of the nuances of indexed views with all the data warehousing I've been doing lately--gotta figure out how to sqeeze every little bit of performance ")
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11889723
I learnt a lot from this too.

The app being developed extracts various counts from a set of transactions for 12 periods for a customer. Prior to the tune up, this was taking around 28 seconds. Now it is fast. Very, very fast. I think the delays come from the web server. I'm not running any optimizations yet, just so I know roughly how slow it CAN be as a base line.

Thanks to you all for the comments. EXTREMELY useful.

Richard.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11890933
If the data is not being edited could you set up a
READ only datbase for this?

Ive heard that that would speed up performance for that DB alot.

Jay
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11891063
Can this facility be toggled? I do need occassional write access during the regular import?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11894531
Ya, it can be toggled....I think  you'll find the read-only attribute doesn't give you the performance boost it should...

this will change to RO

ALTER DATABASE YOURDBNAMEHERE set READ_ONLY with rollback immediate


to change to read write:

ALTER DATABASE YOURDBNAMEHERE set READ_WRITE with rollback immediate


Brett
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question