Systems Engineering and RDBMS

Archive for February 6th, 2007

Time Outs

Posted by decipherinfosys on February 6, 2007

No, we are not talking about the time outs that you give to Kids 🙂  We are talking about the different kind of time outs that can occur in an application.  Broadly speaking, these are the type of time outs that can occur in an application:

  1. Connection Time outs,
  2. Command Time outs (like the command time out in ADO),
  3. Transaction Timeouts (like the COM+ global transaction timeout or class level transaction timeout settings),
  4. Session Time outs (IIS timeouts for example – for a website, a virtual directory, an individual ASP/ASP.Net page) ,
  5. Provider polling timeouts (like the OLEDB connection pool timeouts, KB 237977 from MSFT has more information on this),
  6. From the database side (taking SQL Server as an example):
    1. Lock Time Out : Timing out on the lock depending upon the settings used.
    2. Remote Login timeouts: For the linked server logins.
    3. Remote query timeouts: Again, for the linked servers but for the queries instead of the connections.
    4. Query wait (resource related) and Query Governor Cost Limit (execute/runtime setting and is the query cost threshold setting – helps during the benchmarks).

Posted in DB2 LUW, Oracle, SQL Server | 1 Comment »

Unicode and non-Unicode data-type mis-matches

Posted by decipherinfosys on February 6, 2007

I have seen this at many client sites.  Though it seems to be a trivial issue – many developers do not realize this till their code hits a benchmark run or even worse – directly put into production with just unit tests and no benchmarks.  Consider this:

CREATE TABLE TESTCASE (COL1 VARCHAR(10) NOT NULL)
GO
CREATE INDEX TESTCASE_IND_1 ON TESTCASE (COL1)
GO
DECLARE @I INT
SET @I = 1
WHILE (@I <= 10000)
BEGIN
INSERT INTO TESTCASE VALUES (‘ABC’ + CAST(@I AS VARCHAR(7)))
SET @I = @I + 1
END
GO

So, we created a table with a single column and dumped 10000 unique values into it and then collected the stats since we created the index on it.  Any given value qualifie for only a single record in the table so when we query for that particular value using an equality operator, the optimizer should jump with glee and send it back to us ASAP, right?  Well, not always – if you are not using the right data-type, it can lead to type conversions and you will get a FTS (Full Table Scan) instead of an index seek.  Consider this as an example:

set showplan_text on
go
SELECT * FROM testcase where col1 = N’ABC101′
go
SELECT * FROM testcase where col1 = ‘ABC101’

–Output:

StmtText
—————————————————————————————————————————————-
|–Table Scan(OBJECT:([SVPROD21].[dbo].[TESTCASE]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[SVPROD21].[dbo].[TESTCASE].[COL1],0)=[@0]))

StmtText
—————————————————————————————————————————————
|–Index Seek(OBJECT:([SVPROD21].[dbo].[TESTCASE].[TESTCASE_IND_1]), SEEK:([SVPROD21].[dbo].[TESTCASE].[COL1]=[@0]) ORDERED FORWARD)

This becomes an issue – and depending upon how the applicaton code is written, it could be a very easy fix or a time consuming one.  Remember that if you are not using unicode data-types in your schema, there is no need to declare the parameters as Unicode.  Instead of literal, if you were using a parameterized query (bind variables) above, you will get the same issue since it amounts to the same thing in the end.  I used the literal to just show the point.

declare @par1 nvarchar(10)
set @par1 = N’ABC101′

SELECT * FROM testcase where col1 = @par1

StmtText
——————————————————————————————————————————————-
|–Table Scan(OBJECT:([SVPROD21].[dbo].[TESTCASE]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[SVPROD21].[dbo].[TESTCASE].[COL1],0)=[@par1]))

Posted in SQL Server | Leave a Comment »