1. I have written a query which you can see below. The query qpproximately takes 50- 60 SEC to run,
which I believe is way too high. The total rows returned is 10,000 .
Any idea to improve the query timings ?
The number of rows in each table are as follows :
ACCOUNTS : 1,580
PERSONNEL : 1,616
DIVISION : 15
LABOR_COMMITTED : 23,000
FISCAL_SYSTEM_PERIODS : 20
FISCAL_YEARS : 20
INDEXES :
ACCOUNTS TABLE- 1. ACCOUNT_ID ( Primary Key), 2. IN_AC_COMBINED(ACCOUNT_ID , ACCOUNT_MANAGER) LABOR_COMMITTED TABLE- 1.LABOR_COMMIT_ID ( Primary Key) 2. IN_LC_COMBINED(PERSONNEL_ID, ACCOUNT_ID) PERSONNEL TABLE- 1. PERSONNEL_ID ( Primary key)
The Actual Query :
SELECT AC.ACCOUNT_NUMBER , AC.ACCOUNT_NAME , AC.ACCOUNT_END_DATE , P1.NAME , D.DIVISION_NUMBER , P.SURNAME_ISI || ' '|| P.FIRST_NAME Person_Name, ISNULL(P.EMPLOYEE_ID,P.VISITOR_NUMBER) Employee_Number, CONVERT(DATETIME , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR) Month_Year , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR Month/Year, ROUND(LC.LCS_EFFORT,2) LCS_Effort , 'ACTUAL' AS 'Labor Type' , (SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) STAFF_CAT FROM ACCOUNTS AC(INDEX IN_AC_COMBINED), PERSONNEL P1 ,DIVISIONS D, LABOR_COMMITED LC (index in_lc_combined) , PERSONNEL P , FISCAL_SYSTEM_PERIODS FSP , FISCAL_YEARS FY WHERE P1.PERSONNEL_ID IN (SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT WHERE PARENT.PERSONNEL_ID IN (7167,7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0) AND AC.ACCOUNT_MANAGER = P1.PERSONNEL_ID AND P1.DIVISION_ID = D.DIVISION_ID AND AC.ACCOUNT_ID = LC.ACCOUNT_ID AND LC.PERSONNEL_ID = P.PERSONNEL_ID AND FSP.FISCAL_SYSTEM_PERIOD_ID = LC.FISCAL_PERIOD_ID AND FY.FISCAL_YEAR_ID = LC.FISCAL_YEAR_ID AND LC.COMMITED_DATE IS NOT NULL AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007') AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')
----------------------------------------------------------------------------------------------------------------------------
2. I have a table with 10 million rows. about 9.5 million rows are identified by certain criteria must be deleted from this table.
If i use DELETE statment for this, optimizer takes nearly 15-20 minutes to execute the query,so how can i minimize the execution time?
------------------------------------------------------------------------------------------------------------------------------
3. I was working on a stored procedure for a pending QC . I need to negate the bskSymbol which have multiple consSymbol .
so for that i am using the query given below and saving that in a #table .
select bskSymbol from RSK_IM_ModifyBasket group by bskSymbol having count(*) > 1 and BatchId = 0
or Like ->
select into ... #temporary table
....
bskSymbol not in (select bskSymbol
from RSK_IM_ModifyBasket b
group by bskSymbol
having count(*) > 1 and BatchId = 0
)
does not produce any one to one mapping of bskSymbol and consSymbol .
where as the query given below shows some bskSymbol columns with one to one mapping of bskSymbol and consSymbol . by one to one mapping i mean for some bskSymbol we have count value as 1 .
select bskSymbol,count(consSymbol) from RSK_IM_ModifyBasket where BatchId = 0 group by bskSymbol
can u suggest where am i wrong ?.
---------------------------------------------------------------------------------------------------------------------------------------------------------
Hi friends,
I am facing a problem with bcp process on the following environment
OS - sun solaris
database - sybase
I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
any suggestion will be appriciated.
sol:
think you might be limited by your disk I/O speeds. But things you can use to try and speed up the process is a fifo special file (man mkfifo) as the bcp file and a larger network packet size (bcp -A 8704)
Would it be worth altering your unix script to do one table at a time. It might avoid the competition for resources. It would be good to time each stage so you can see where the issue is.
Do you drop the indexes on the destination table before bcping in, then create them again afterwards? hoping to use fast bcp.
Another option might be to try putting the 13 tables into their own database and simply dump the database at the source and restore it on the destination rather than bcping in and out each table.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Does anyone know of an easy way to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Showing posts with label Problem's. Show all posts
Showing posts with label Problem's. Show all posts
Sybase problems 2
Cursor - Not fetching next record
Posted: Apr 13, 2007 5:45 PM Reply
Hi,
I am BusinessObjects Developer and new to Sybase-IQ.
I had created on stored procedure and facing an issue that the cursor is not fetching next record.
Can anybody help to debug this issue.
Below is the stored procedure.
declare @catcode varchar(12);
declare @MX integer;
declare @MTH integer;
declare @Yr integer;
declare @Yr_Max integer;
declare @Yr_Min integer;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;
open CRS_catcode;
set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;
CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;
if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
if sqlstate = err_notfound then
print sqlstate;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
close CRS_catcode
end
end
-------------------------------------------------------------------------------------------------------------
Re: Cursor - Not fetching next record
Posted: Jul 12, 2007 5:34 PM Reply
hi dear
u have not use fetch next statement in your sp before the close of the cursor
try this , i think it will help u
/* Notice: Formatted SQL is not the same as input */
(52,32)then
DECLARE @catcode VARCHAR(12);
DECLARE @MX INTEGER;
DECLARE @MTH INTEGER;
DECLARE @Yr INTEGER;
DECLARE @Yr_Max INTEGER;
DECLARE @Yr_Min INTEGER;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;
open CRS_catcode;
set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;
CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;
if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
IF sqlState = Err_NotFound
IF sqlState = Err_NotFound Then
PRINT sqlState;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
----------------------------------------------
fetch next CRS_catcode
into @catcode ;
-----------------------------------------------
end
close CRS_catcode
end
Posted: Apr 13, 2007 5:45 PM Reply
Hi,
I am BusinessObjects Developer and new to Sybase-IQ.
I had created on stored procedure and facing an issue that the cursor is not fetching next record.
Can anybody help to debug this issue.
Below is the stored procedure.
declare @catcode varchar(12);
declare @MX integer;
declare @MTH integer;
declare @Yr integer;
declare @Yr_Max integer;
declare @Yr_Min integer;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;
open CRS_catcode;
set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;
CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;
if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
if sqlstate = err_notfound then
print sqlstate;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
close CRS_catcode
end
end
-------------------------------------------------------------------------------------------------------------
Re: Cursor - Not fetching next record
Posted: Jul 12, 2007 5:34 PM Reply
hi dear
u have not use fetch next statement in your sp before the close of the cursor
try this , i think it will help u
/* Notice: Formatted SQL is not the same as input */
(52,32)then
DECLARE @catcode VARCHAR(12);
DECLARE @MX INTEGER;
DECLARE @MTH INTEGER;
DECLARE @Yr INTEGER;
DECLARE @Yr_Max INTEGER;
DECLARE @Yr_Min INTEGER;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;
open CRS_catcode;
set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;
CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;
if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
IF sqlState = Err_NotFound
IF sqlState = Err_NotFound Then
PRINT sqlState;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
----------------------------------------------
fetch next CRS_catcode
into @catcode ;
-----------------------------------------------
end
close CRS_catcode
end
Sybase problems
1. I am facing a problem with bcp process on the following environment
OS - Linux
database - sybase
I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
------------------------------------------------------------------------------------------------------------------------------------------------------------
2. I am having five user in each Sybase server,Now i want to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers how do i do this in efficient way.
------------------------------------------------------------------------------------------------------------------------------------------------------------
3. I have the following procedure:
create PROCEDURE sp_SEARCH_Contragents(@nSPFirmID integer,@nFilterID integer,@szName varchar(255)=null,@szTaxNum varchar(255)=null,@szBULSTAT varchar(255)=null,@szFullCode varchar(255)=null,@szCity varchar(255)=null,@szPhone varchar(255)=null,@szAddress varchar(255)=null)
as
begin
declare @szNameSign varchar(2),
@szTaxNumSign varchar(2),
@szBULSTATSign varchar(2),
@szFullCodeSign varchar(2),
@szCitySign varchar(2),
@szPhoneSign varchar(2),
@szAddressSign varchar(2)
create table #tResult(
Name varchar(255) null,
TaxNum varchar(30) null,
BULSTAT varchar(10) null,
FullCode varchar(100) null,
City varchar(255) null,
Phone varchar(100) null,
Address varchar(255) null,
FoldID integer null,
)
insert into #tResult( Name,TaxNum,BULSTAT,FullCode,City,Phone,Address,Fo ldID)
select..........from where.............
insert into #tResult(Name,TaxNum,BULSTAT,FullCode,City,Phone,A ddress,FoldID)
select.................. from where..............
select Name as Name,
TaxNum as TaxNum,
BULSTAT as BULSTAT,
FullCode as FullCode,
City as City,
Phone as Phone,
Address as Address,
FoldID as FoldID
from #tResult
end
If I execute the procedure from ISQL with some input values it is working correctly. But if I try to execute it from Java like
ResultSet res = stmt.executeQuery("exec sp_SEARCH_Contragents");
or like this
CallableStatement cs = conn.PreparedCall("{call sp_SEARCH_Contragents)}");
I don't get any rows or error,could you fick the problem where i am having ?
OS - Linux
database - sybase
I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
------------------------------------------------------------------------------------------------------------------------------------------------------------
2. I am having five user in each Sybase server,Now i want to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers how do i do this in efficient way.
------------------------------------------------------------------------------------------------------------------------------------------------------------
3. I have the following procedure:
create PROCEDURE sp_SEARCH_Contragents(@nSPFirmID integer,@nFilterID integer,@szName varchar(255)=null,@szTaxNum varchar(255)=null,@szBULSTAT varchar(255)=null,@szFullCode varchar(255)=null,@szCity varchar(255)=null,@szPhone varchar(255)=null,@szAddress varchar(255)=null)
as
begin
declare @szNameSign varchar(2),
@szTaxNumSign varchar(2),
@szBULSTATSign varchar(2),
@szFullCodeSign varchar(2),
@szCitySign varchar(2),
@szPhoneSign varchar(2),
@szAddressSign varchar(2)
create table #tResult(
Name varchar(255) null,
TaxNum varchar(30) null,
BULSTAT varchar(10) null,
FullCode varchar(100) null,
City varchar(255) null,
Phone varchar(100) null,
Address varchar(255) null,
FoldID integer null,
)
insert into #tResult( Name,TaxNum,BULSTAT,FullCode,City,Phone,Address,Fo ldID)
select..........from
insert into #tResult(Name,TaxNum,BULSTAT,FullCode,City,Phone,A ddress,FoldID)
select.................. from
select Name as Name,
TaxNum as TaxNum,
BULSTAT as BULSTAT,
FullCode as FullCode,
City as City,
Phone as Phone,
Address as Address,
FoldID as FoldID
from #tResult
end
If I execute the procedure from ISQL with some input values it is working correctly. But if I try to execute it from Java like
ResultSet res = stmt.executeQuery("exec sp_SEARCH_Contragents
or like this
CallableStatement cs = conn.PreparedCall("{call sp_SEARCH_Contragents
I don't get any rows or error,could you fick the problem where i am having ?
Subscribe to:
Posts (Atom)