Solved

Speeding up queries.

Posted on 2004-08-23
20
430 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:RQuadling
  • 8
  • 5
  • 4
  • +1
20 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 400 total points
Comment Utility
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:RQuadling
Comment Utility
Excellent comments.

Thanks.
0
 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 100 total points
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
Not obvious.

Why can't all indexes be clustered?

0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 40

Author Comment

by:RQuadling
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
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
Comment Utility
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:RQuadling
Comment Utility
Can this facility be toggled? I do need occassional write access during the regular import?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now