SQLTeam.com | Weblogs | Forums

Suggestions on Performance Tuning

I would like to have some performance tuning on the below SQL - Please suggest

DECLARE SQLCODE,V_SQLCODE INTEGER DEFAULT 0;
DECLARE C_GETCAR CURSOR WITH RETURN FOR

SELECT C.CARNBR,C.ACCIPK,C.MSPIPK,C.LNKIPK AS PERIPK,C.CARTYP AS CARTYP,
CASE C.CARSTS WHEN 0 THEN 'ACTIVE'
WHEN 999 THEN 'INACTIVE' END AS CARSTS,
CASE A.CPYNBR WHEN 1 THEN 'US'
WHEN 2 THEN 'CA' END AS CPYNBR,
C.CAEBDT,C.CADIDT
FROM P23ADTDT1.MECABSP C
LEFT OUTER JOIN P23ADTDT1.MEACBSP A
ON C.ACCIPK = A.ACCIPK
WHERE C.CARNBR = CAST(IO_CARNBR AS DECIMAL(15,0)) AND C.LKFLCD = 100 WITH NC;

OPEN C_GETCAR;
SET V_SQLCODE = SQLCODE;
CASE
WHEN V_SQLCODE = 0 THEN
SET OUTMSG = 'SUCCESS';

WHEN V_SQLCODE = 100 THEN
SET OUTMSG = 'NOT FOUND';

WHEN V_SQLCODE = -502 THEN
CLOSE C_GETCAR;
OPEN C_GETCAR;
ELSE
SET OUTMSG = 'DATABASE ERROR';
END CASE;
END P1;

indexes

Few joins only at a time

try to filter first before using ... in join
instead of ... table A join
( select * from Table A where name = 'abc' ) join

please google search .. lots lots of stuff .. but PAIN going through to find your needs solution

There is no error coming up, I just want to know if I can change any statements for better performance

is this Microsoft SQL Server?

Cosmos DB

this microsoft sql server forum. but maybe someone with cosmos db experience might be able to help you here. or

https://social.msdn.microsoft.com/Forums/en-US/home?forum=azurecosmosdb

do you really need to use a cursor??