Select with long varchar - very slow. Very weird

[hansg]

Does somebody know why a SELECT statement, including "LONG VARCHAR" takes a huge time to execute and why the executions times drops dramatically when I use a LEFT()?

UPDATE: problems is at the Client side. When I do a SELECT with long varchars, even if the values are all NULL, it returns a much more bytes then when I have a varhar(1000). I checked that with: SELECT CONNECTION_PROPERTY ( 'BytesReceived' );

Is this is bug?

---

I have a table with a few "long varchar" columns. Most of them are NULL, some have a value with a short string (max is 66 characters). The table includes 1.000.000 rows.

The problem is that when I include these long varchar columns in my SELECT the query takes a long time to execute. (I use dbisql, in options I set truncate set to 0, retrieve all rows). This query runs 13 seconds and returned 45.000 rows: SELECT id, int_column, longvarchar_1, longvarchar FROM test_table WHERE int_column BETWEEN 10 AND 50; When I add a LEFT() for the long varchars the execution time drops from 13 to 1 second, still 45 rows with the same data. SELECT id, int_column, LEFT(longvarchar_1, 1000), LEFT(longvarchar, 1000) FROM test_table WHERE int_column BETWEEN 10 AND 50;

[VolkerBarth]

Is there an index on int_column? (Don't know whether that will make a difference for the BLOB data access.)

[hansg]

Nothing to do with int_column. It is just an example. I am curious if you reproduce the same in a database with a table with a long varchar. I can reproduce this problem in any SA10 or SA16 database in any table. Important is that you set the options in dbisql well; no truncate and retieve all rows.

Even if add two "long varchar" columns in the demo16 database I can see a different in execution time. alter table SalesOrderItems add "longv_1" long varchar default null ; alter table SalesOrderItems add "longv_2" long varchar default null ; select id, longv_1, longv_2 from SalesOrderItems; exeuctes in 0.04x seconds.

select id, left(longv_1, 1000), left(longv_2, 1000) from SalesOrderItems; executes in 0.015 seconds.

[Breck_Carter]

What is the client?

It is unlikely to be a problem on the server side since the performance of LONG VARCHAR values depends on the actual string value length.

[hansg]

Client is a different PC then than the database server is running (by lan network). When I execute the SELECT at the same PC were the database server is running the performance is good. The problem seems that the packet size is very large. I tested with around 10.000 rows, two columns, both long varchars with only NULL values. With 'BytesReceived' i found that more then 800.000 bytes were sent. When I us a LEFT(xx, 1000) the bytes drops to 18.000 bytes.

[Breck_Carter]

What exactly is the client software being used?

[MarkCulp · Accepted solution]

I believe the issue with the LONG VARCHAR column is that, well, it is long. I.e. unknown length.

When all columns returned by a query are relatively short (less than 32K) then the server can fetch all of the column data for multiple rows in one round trip to the server even before the client has requested the database for each of these rows. This is called "prefetching the rows". The number of rows that are prefetched has changed over the years but can vary from 10s of rows to 1000s of rows.

When the described result set contains a wide column then prefetching will not be used because the wide data must be fetched using "get data" calls to the server. I.e. each wide row must be fetched individually from the server and each of these fetches is another round trip to the server. The lack of prefetch and the additional round trips to get the wide data can result is query fetches taking considerable amount of extra time. Even then the resulting data is not wide and can be fetched in the original row fetch (i.e. a prefix of each wide column will be fetched in each row fetch) the lack of prefetching will result in longer execution times.

When you add the LEFT( long-varchar-col, 1000 ) then the described width of the column is CHAR(1000 CHAR) and hence falls under the 32K limit and therefore prefetching can again be used.

FWIW: You can see how your queries are being described using the sa_describe_query() procedure.

HTH

[hansg · Accepted solution]

Thx for answering. When the SQL is executed at the PC of the database server it performs well. Only on another PC it is terrible. See my last comment at Breck Carter.

[MarkCulp · Accepted solution]

Same machine round trip time between client and server (which will likely be done through shared memory connection) will be much better than network round trips so this is consistent with my suggestion that it is the lack of prefetching (and increased number of round trips to the server) that is causing the performance difference.

[Former Member · Accepted solution]

Mark is very informed and very correct in what he is telling you. Further .... Even if your local connection happens to be a tcp/ip network connection that is not going "over the wire" but is being handline "on-the-stack" and thus in-memory and over the system busses which will perform much much faster than anything sent over the wire.

[hansg · Accepted solution]

Okay, but what would you suggest to do? I am thinking about altering the database and change data type from "long varchar" to "varchar(1000)". That would solve my problem. Of course, only for columns that would not need values larger then 1.000 characters.

Do you agree ?

[MarkCulp · Accepted solution]

Yes, if you know that the column character data will always be less than 1000 characters in length then it would be good to declare the column as varchar(1000). In general, if the character data in a column will always be <= X characters then declaring the column as varchar(X) is recommended. Do this not only helps the database server process the data more efficiently but also helps client applications that work with the data.