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;
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.