Database

1-Oct-03 8:32

I have created a sql script select statement which when run as a script in query analyzer brings back results in less then a second. When the same script is put into a stored procedure and that is executed in query analyzer the server is not able to bring back results in a timely manner. I stop the query after 20 seconds because it kills the server. My guess is that the problem is related to indexing and the stored procedure is compiling on the wrong index. Is there a way to force the stored procedure to compile on the correct index? I have added several ‘with(Index=’???’)’ clauses it has had no affect on the results. Anyone have a suggestion? Thanks

Jason W.

andyharman1-Oct-03 12:01

andyharman

1-Oct-03 12:01

Can you post the stored procedure here, with details of the indexes that you think should be used and the number of rows expected from each of your tables.

Query Analyzer contains an option to show a graphical "Explain Plan" view of how it is accessing the tables and indexes in your database. If you don't know how to use this then I would heartily suggest that you spend some time become familiar.

From experience, SQL-Server seems prone to using hash-joins to link tables together. Sometimes rewriting your query from:

select * from Department D<br />
inner join Employee E<br />
on E.DeptId = D.DeptId

to:

select * from Department D<br />
inner loop join Employee E<br />
on E.DeptId = D.DeptId

where "loop" is the optimizer hint for a nested-inner join yields better results (if supported by indexes). I also normally order the from clause in the sequence that I would expect the query-optimizer to work (it shouldn't make any difference - but it does serve as useful documentation to other programmers who look at my code).

Tuning queries is a bit of an art-form - there are many different techniques available (clustered indexes, covered indexes, hints, juditious use of temporary tables, inline-views, etc.). I would need to know more details about your specific situation before being able to give a better answer.

Andy

Jason Weibel2-Oct-03 12:09

2-Oct-03 12:09

I just tried the stored procedure using the loop join and it is 100 times faster. I hadn’t heard of that type of join before. Can you recommend a good web site or book where I can get more information on developing databases and stored procedures maximizing performance?
Thanks for your detailed response.

Jason W.

Mike Dimmick2-Oct-03 12:49

Mike Dimmick

2-Oct-03 12:49

loop doesn't indicate a different type of join - it causes the underlying query interpreter to use a different logical operator to perform the operation.

It appears that the original query (without the hint) was using a hash-match join: hashing the data selected from one side of the join and comparing to a table of hashes generated from the other side of the join.

Adding the loop hint causes it to use the Nested Loops operator, which is the simplistic way to implement a join: consider each row on the left-hand side in turn, look through the right-hand side to see if anything matches. The loop over the right-hand side is nested inside the loop for the left-hand side; hence, Nested Loops (although SQL Server may decide to swap the tables or indexes over if appropriate).

If there isn't much data on at least one side of the join, nested loops can perform better than hash match. The downside of nested loops is simply that the server must iterate over every row of one side the number of times that there are rows on the other side.

When compiling a stored procedure, SQL Server caches the compiled execution plan. It does this the first time that the procedure is executed. When pressure for memory increases, SQL Server removes any pages that haven't been touched recently from cache - including cached execution plans.

However, when compiling the procedure for the first time, SQL Server is guided by the parameters. If the parameters will be very different, causing greatly different execution paths, the cached execution plan can be terrible. To force a recompile every time, add WITH RECOMPILE before the AS keyword in the definition of the procedure. As always, you should measure the effect this has.

Another reason the query optimizer might pick poor indexes is if your index statistics are not up to date. Generally SQL Server updates statistics automatically, but you might have turned this off. Look up UPDATE STATISTICS and sp_autostats. Alternatively, consider updating statistics via a scheduled maintenance job. The statistics tell SQL Server how selective an index is, and thus how useful it is in finding rows.

If your execution plan indicates that SQL Server is having to join two indexes on one table together - perhaps seeking one and scanning the other - and your query didn't join the table onto itself, you may find that defining a new index that includes all the columns projected or referenced (excluding the primary key columns, if this table has a clustered index) may help. If a single index contains all the data required, the query is referred to as a covered query. SQL Server can handle a covered query simply by reading the index - it never has to read the table, since all data is stored in the index.

I mention excluding the primary key columns if a clustered index. This is because, if a table has a clustered index, SQL Server stores the clustered index data as the 'destination' part of all non-clustered indexes - to find the rest of the row, after reading the non-clustered index, it has to seek through the clustered index. If there isn't a clustered index, it stores the row number instead.

Care should be taken, because inserts, updates and deletes take more time the more indexes are present.

For more information, try http://www.sql-server-performance.com/[^] or Inside SQL Server 2000[^] by Kalen Delaney (MS Press) - also see the book's website[^].

Jason Weibel3-Oct-03 6:08

3-Oct-03 6:08

Thanks for the detailed response. I am still trying to figure out what a hint is, andy’s message made reference to it also, I will look into that. I have taken a quick look through at the http://www.sql-server-performance.com site you recommended it appears to have a great deal of content. Taking a look at a few of the articles there is a lot I need to learn.
Again, I truly appreciate the your help.

Jason W.

Mike Dimmick3-Oct-03 6:27

Mike Dimmick

3-Oct-03 6:27

Hint \Hint\, v. i.
     To make an indirect reference, suggestion, or allusion; to
     allude vaguely to something.

(from www.dict.org[^]).

Basically, it's a non-compulsory suggestion to SQL Server's query optimiser about how to perform the query (although locking hints are compulsory). Much like using inline or register in C++ - the compiler may choose to follow your suggestion, but it may ignore you.

Jason Weibel3-Oct-03 6:44

Database documentation tools

3-Oct-03 6:44

Ok I was getting the impressing that hints was a key word in sql. Correct me if I’m wrong but when you say hints it means using something like ‘with(index=ID_Index)’, in my script. When I uses this type of statement it is telling the compiler that I think it should use this index but the compiler can over ride that if it wants.

Jason W.

Michael P Butler1-Oct-03 7:19

Michael P Butler

1-Oct-03 7:19

I'm looking for a tool that will read an SQL database schema and create a word/text/html document of the table definitions. Visio is okay but there is no easy way for it to print the field descriptions.

Basically something that will give me Field, Type, Size for all the tables in the schema in an editable document format.

Michael

'Logic, my dear Zoe, merely enables one to be wrong with authority.' - The Doctor: The Wheel in Space

Re: Database documentation tools

Mike Dimmick2-Oct-03 3:49

Mike Dimmick

2-Oct-03 3:49

This is a bit tangential: an article in August's MSDN Magazine[^] talks about generating an XML file from a SQL Server data schema.

You should then be able to use an XML parser, or XSLT, to translate into the format you want.

Re: Database documentation tools

Michael P Butler2-Oct-03 22:47

Michael P Butler

2-Oct-03 22:47

Thanks. I'd read the article a few weeks back whilst investigating code generation. I'd been hoping to find an app that already produced the documentation for me. Oh well, looks like I'm going to have to write some code. I'll try and combine it with my code generation ideas to produce a nice integrating solution.

Thanks again,

Michael

'Logic, my dear Zoe, merely enables one to be wrong with authority.' - The Doctor: The Wheel in Space

Convert Access97 to 2000 programmatically

Hans-Georg Ulrich1-Oct-03 5:37

Hans-Georg Ulrich

1-Oct-03 5:37

I would like to change my app from DAO35 to DAO36 or higher, to support ACCESS2000 DBs. How to convert a existing ACCESS97 database programmatically (using DAO)? Is there anything comparable to the RepairDatabase / CompactDatabase calls ?
Thank you.

Re: Convert Access97 to 2000 programmatically

Mike Ellison2-Oct-03 10:16

Mike Ellison

2-Oct-03 10:16

Couldn't you just open each database in Access 2000 and select "convert" when asked to open or convert? How many databases are you talking about?

error in class type

ranjjj1-Oct-03 5:02

ranjjj

1-Oct-03 5:02

the following is the code written to add data into the database!
i get the error saying
COleSafeArray undefined..
i there any need to include any other header files fort this to work.
ADO functions are used in this c++ client!

COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);

// Fill the fields name now
long lArrayIndex[3];
lArrayIndex[0] = 0;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("kEy")) );
lArrayIndex[0] = 1;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("nAme")) );
lArrayIndex[0] = 2;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("tYpe")) );
lArrayIndex[0] = 3;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("vAlue")) );

// Create an array of values to be added
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);

//Fill the values in each field
lArrayIndex[0] = 0;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("rob")) );
lArrayIndex[0] = 1;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("surya")) );
lArrayIndex[0] = 2;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("kakka")) );
lArrayIndex[0] = 3;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("mac")) );
// call Addnew of recordset now
pRecordset->AddNew( vaFieldList, vaValueArray );
pRecordset->Close();

ranjani

bcp

sardinka1-Oct-03 3:07

sardinka

1-Oct-03 3:07

question about BCP process.
The problem is when I transfer the data in date time type field the date '00000000' is not transferable, which is understandable, however it also kicking out some data with appropriate date. Any idea why it occur?

Updating Related Tables

Wjousts30-Sep-03 11:07

30-Sep-03 11:07

I have three tables, a main table and two subtables that are related to the main table by the main tables key field. In my application I set up three OleDbDataAdapters to read three seperate tables into three tables of a dataset and then I add to the datasets relation collection to link the tables together. The problem is that while I can update the main table by calling the update method of it's DataAdapter, I can't do the same with the other two tables. When I call their update method, nothing happens. I get no errors and the rows that where marked to be updated are still marked to be updated (i.e. AcceptChanges hasn't been called). Also, I can't change the key field even in the main table. Changes to the key simply get ignored and don't get written back to the database. Does anybody know what is happening here? I suspect it's something to do with the tables being related.

STW2-Oct-03 7:29

STW

2-Oct-03 7:29

Please show your code which updates the Child Tables.

Stefan

Wjousts2-Oct-03 8:54

2-Oct-03 8:54

Okay, thanks for your reply. Here's an example of the problem I'm having. I've included some of the code I've added just to try and figure out what's happening. When I iterate throw the rows of the child table I find that all the rows have RowState = Unchanged, even though some of them have an Original and a Proposed value. I believe they are not getting updated because the RowState is Unchanged, but I can't figure out why the RowState is Unchanged when there is clearly a Proposed value available. Surely setting a Proposed value for a row should automatically change the RowState?

<br />
public void UpdateData()<br />
{<br />
	connDB.Open();<br />
        // I tried to intecept the RowUpdating events, but they never actually occur!<br />
	SubApt.RowUpdating += new OleDbRowUpdatingEventHandler(MyRowUpdateHandler);<br />
<br />
        // Try to find out what the status of the rows in the child table actually is.<br />
	string s = "";<br />
	foreach (DataRow thisRow in m_myDS.Tables["tblSub"].Rows)<br />
        {<br />
		s += thisRow["Key"] + " -> " + thisRow["Field1"] + " " + thisRow.RowState.ToString() + "\n";<br />
                // RowState is ALWAYS Unchanged even though some rows have a proposed value!<br />
		if (thisRow.HasVersion(DataRowVersion.Proposed))<br />
		{<br />
                // The proposed value really is the value it's supposed to be changed to, and the original value<br />
                // really is the original value<br />
			s += thisRow["Field1", DataRowVersion.Original] + " " + thisRow["Field1", DataRowVersion.Proposed] + "\n";<br />
		}<br />
	}<br />
	MessageBox.Show(s);<br />
<br />
        // the main table updates just fine<br />
	MainApt.Update(m_myDS.Tables["tblMain"]);<br />
        // but this does nothing!!! It doesn't matter which way around I update the two tables, or even if I leave<br />
        // out the main table altogether<br />
	SubApt.Update(m_myDS.Tables["tblSub"]);<br />
	connDB.Close();<br />
}<br />

STW2-Oct-03 22:31

STW

2-Oct-03 22:31

1. Did you call AcceptChanges somewhere? Don't call AcceptChanges before Adap.Update().
2. Clear the EventHandler for RowUpdate. This may call AcceptChanges()
3. Try to edit a Row and immediatly afterwards check it's RowState. If the RowState is unchanged I don't know what's wrong. AcceptChanges is a tricky thing: after this call all Rows are marked unchanged! So there is nothing what could be Updated by the Update Method of Apt.

Please inform me about Point 3.

Stefan

Wjousts3-Oct-03 4:52

3-Oct-03 4:52

STW wrote:
1. Did you call AcceptChanges somewhere? Don't call AcceptChanges before Adap.Update().

No, I have not called AcceptChanges explictly anywhere in my program. I had thought that perhaps calling update on the main table might automatically call AcceptChanges on the child table, but not updating the main table didn't fix the problem. I was also thinking that calling AcceptChanges should move the proposed value into the original value shouldn't it? That's not been happening.

STW wrote:
2. Clear the EventHandler for RowUpdate. This may call AcceptChanges()

I tried it without the EventHandler and it makes no difference. I only put the EventHandler there in the hopes of being able to intercept the call and figure out what was happening. As it turns out, the event never gets fired.

STW wrote:
3. Try to edit a Row and immediatly afterwards check it's RowState. If the RowState is unchanged I don't know what's wrong. AcceptChanges is a tricky thing: after this call all Rows are marked unchanged! So there is nothing what could be Updated by the Update Method of Apt.

Okay, I tried manually changing the value of one field in the child table like this:

<br />
m_myDS.Tables["subtable"].Rows[0]["Field1"] = 101;<br />

And if I then iterate through the rows in the table, I can see that row has been changed to RowState.Modified and it doesn't have a proposed value anymore. Further, when I call the update that row does, in fact, get updated in the database.

Wjousts3-Oct-03 5:20