Speeding up queries.

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.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BillAn1Connect With a Mentor Commented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Excellent comments.

Thanks.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jay ToopsConnect With a Mentor Commented:
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
 
BillAn1Commented:
Yes, you can simply pase in anu number of queries into the QS window and it will analyse them all together for you
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
 
Jay ToopsCommented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Not obvious.

Why can't all indexes be clustered?

0
 
BillAn1Commented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
 
arbertCommented:
"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
 
Jay ToopsCommented:
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
 
arbertCommented:
"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
 
Jay ToopsCommented:
Hmm .. COOL..
I'll file that away for future use.

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

Jay
0
 
arbertCommented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
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
 
Jay ToopsCommented:
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
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Can this facility be toggled? I do need occassional write access during the regular import?
0
 
arbertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.