select * - slow 1

[DebiJo]

I have a 3rd party package that we have been using for about a year. I have one table that has 70,000 records. This table has 28 fields of various types.

Select * from this_table takes about 4 minutes. selecting only 1 field takes less than 10 seconds. We have done various combinations of fields, some will complete in 10-30 seconds, and others 4 minutes. I have not found the pattern. You can find one field that's slow, combine it some others and it's fast. Take something away and it's slow again. I'm confused. The 3rd party people are blaming it on SQL. The SQL server is not stressing at all. I tend to think it's something with their design or the data contained in the table.

One 'weird' thing, if it matters, they have several text fields that appear (in the program) as unlimited length. We have some that contain several paragraghs of information. However, the table design shows the field with a size of 16.

Any ideas as to why SQL would run a select * query slow?

SQL 2000 SP3a
Windows 2000 SP4
Dual processor
2 gig RAM

Thanks in advance,
Debi

 

[craigfw]

If nothing else, SELECT * is selecting all the data from all of your rows. That is 70,000 rows x 28 columns = 1.96 million / 4 minutes (240 seconds) = 8,16666...7 rows per second. It also depends on your system and if it is dedicated to the SQL Server or sharing resources.

 

[DebiJo]

Yes, it's a dedicated SQL Server. The CPU/memory/disk IO have been monitored, nothing seems to be pegging. The combinations of fields really confuses me...

select field1,field3 from this_table is slow
select field2,field3 from this_table is slow
select field1,field2 from this_table is fast
select field1,field2,field3 from this table is slow
select field3 from this_table is fast

I really don't get it.

Thanks again,
Debi

 

[SQLBill]

Check field3, is it a computed field?

-SQLBill

 

[SQLBill]

Another thing, are you really selecting from a table or is from a view? (if from a view, then field3 might be from a different table than the other fields).

-SQLBill

 

[DebiJo]

If I go into the table properties, I see field types of: char, text, int, datetime. Is that where I would see if they were calculated fields or not?

Thanks again,
Debi

 

[DebiJo]

I actually see this table inside the "tables" window under the database. So, I guess from this it is actually a table and not a view.

Thanks again!
Debi

 

[SQLSister]

What specifically is the data type of the slow field?

What indexes if any do you have on the table?

What is the difference in the execution plans bewteen the slow queries and the fast ones? I suspect that the differnce is that some are using indexes and some are not.

It concerns me that even selecting just one field you have such a slow response. Are you doing this over a network or dirctly on the SQL Server machine. That might tell you if the problem lies in part in your network vice the server.

Select * is not usually a good query techinque as it will generally be less efficient than selcting only the minimum number of columns you need. Also most selct queries do better with a where clasue to help them use the indexes.

One 'weird' thing, if it matters, they have several text fields that appear (in the program) as unlimited length. We have some that contain several paragraghs of information. However, the table design shows the field with a size of 16.

This is not wierd; it is the normal behavior. With text fields you can store much more than the 8000 characters that the varchar datatype stores, however there is a price to pay for this and it is slower access as text fields store their information outside the table and it must be looked up. This could be part of the casue of your slow access type if you have a lot of records where this data is stored. If most of your dat ais smaller than 8000 charcters, you can try the following from BOL:

Using text in row
In Microsoft SQL Server 2000, users can enable a text in row option on a table so it could store text, ntext, or image data in its data row.

To enable the option, execute the sp_tableoption stored procedure, specifying text in row as the option name and on as the option value. The default maximum size that can be stored in a row for a BLOB (binary large object: text, ntext, or image data) is 256 bytes, but values may range from 24 through 7000. To specify a maximum size that is not the default, specify an integer within the range as the option value.

text, ntext, or image strings are stored in the data row if the following conditions apply:

text in row is enabled.


The length of the string is shorter than the limit specified in @OptionValue


There is enough space available in the data row.
When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL Server does not have to access separate pages to read or write the BLOB string.

If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row still apply though: There must be enough space in the data row to hold the pointers.

For more information, see sp_tableoption.

I can;t vouch for this increasing the access speed becasue I have never had to do this. But it might be worth a try. And does the field really need to be text datatype?

Questions about posting. See faq183-874

 

[DebiJo]

All queries are being done directly on SQL box. We are using the select * to try to troubleshoot why our "real" queries are running slow. If select * runs anywhere from 10 seconds to 4 minutes, we can guess our others queries would do the same.

Also, we can't pin it down to just one field. We have ran all combinations of fields and I can't find a pattern as to what is fast and what is slow.

One other thing, we ran a SELCT * ... INTO another table and it ran in 20 seconds. But the select * from the table itself to display on the screen runs in 4 minutes.

Thanks again,
Debi

 

[SQLSister]

Areas i would look at then are indexing, fragmentation, and outdated statistics as well as what I mentioned above about enabling the text in a row option. You might need to get a good book on performance tuning because you are gettting out of the realm of the simple to diagnose (like using a cursor).

Questions about posting. See faq183-874

 

[DebiJo]

We did try re-indexing. That had no effect. I have tried this same select on 3 different SQL servers, using backup and restore each time to get the database there. So, I don't think fragmentation is an issue. Also, I do have plenty of free disk space. I can't really change the field types, as it is a 3rd party package.

Could special characters like *, ", &, or ' at the beginning of text fields cause a problem like this?

Thanks again,
Debi

 

[DebiJo]

Now my 3rd party software provider is saying that SQL has a problem with an asterisk (*) as the first character of a text field. Anyone heard of that before?

Thanks again,
Debi

 

[DebiJo]

I did the 'text in row' and made an update to all 4 text fields in the table. The select * query, which used to run in 4-5 minutes now takes 6 seconds. So, I can vouch for it increasing the access speed.

However, now a couple of parts of the application will not work. But I will send these results to the software provider and maybe they can figure something out.

Thanks for the hint SQLSister.
Debi