Floting Point | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Floting Point

Before convert one database from SQL 7.0 to SQL 2000, profile show something like: sp_execute 38, 1861942., ‘OP’ After convert, sp_execute 21, 1.876770000000000e+006, ‘OP’ What I’m missing? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
SQL 2k changed the default precision level for decimal data type from 38 to 28. Maybe worth looking into, but im not sure, there may be other reasons as the number has changed a fair amount SELECT @@MAX_PRECISION
I guess you need to provide more information, Luis.
———————–
–Frank
http://www.insidesql.de
———————–
I feel BOL will have it. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Here is:<br /><br />In SQL 7.0 the followind query take short time to execute (I remove loop)<br /><br />SELECT NROTRANS FROM TRANSAC (NOLOCK) WHERE NROTRANS=18742000<br /><br />Execution plan:<br /><br />StmtText <br />——————————————————————————————————————————————————————– <br /> |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bark].[dbo].[TRANSAC].[IXC03.10.17_TRANSAC_NroTrans_NroTranselim_CodCtacte_CodEmp]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TRANSAC].[NROTRANS]=18742000) ORDERED FORWARD)<br /><br />In SQL 2000 take longer time.<br /><br />SELECT NROTRANS FROM TRANSAC (NOLOCK) WHERE NROTRANS=1.874200000000000e+006<br /><br /><br />|–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1002], [Expr1003], [Expr1004]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1002]=Convert(1.8742e+006)-1, [Expr1003]=Convert(1.8742e+006)+1, [Expr1004]=If (Convert(1.8742e+006)-1=NULL) then 0 else 6|If (Convert(1.8742e+006)+1=NULL) then 0 else 10))<br /> | |–Constant Scan<br /> |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Bark].[dbo].[TRANSAC].[IXC03.10.17_TRANSAC_NroTrans_NroTranselim_CodCtacte_CodEmp]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[TRANSAC].[NROTRANS] &gt; [Expr1002] AND [TRANSAC].[NROTRANS] &lt; [Expr1003]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />Convert([TRANSAC].[NROTRANS])=1.8742e+006) ORDERED FORWARD<br /><br />Nrotrans int in both cases.<br /><br />Table has PK_Nrotrans clustered.<br /><br />1) Could be the razon for extra time?<br />2) Why SQL don’t choose PK_Nrotrans?<br /><br /><br />Is a third party software, so I only can work with indexs, etc.<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />
It appears that something in the app must have changed. It is now passing a different datatype than INT. This is the reason for the different execution plans. ———————–
–Frank
http://www.insidesql.de
———————–
]]>