IQ Server - slow query on union view

Author

slow query on union view

Konstantin

2005-07-29, 7:24 am

Hi

Can somebody help me with the following problem:

1. I have an ASE Table Deals_Ase and an ASE proxy table
Deals_Iq wich realtes IQ table OldDeals (wich has the same
structure as Deals_Ase table and contanis archived deals)

2. I have also a view Deals_View :

create view Deals_View
as
select * from Deals_Iq
union
select * from Deals_Ase

My problem is that queries on Deals_View like f.e.

select reportDate, count(*) from Deals_View group by
reportDate

becomes frozen with the error message:

Space available in the log segment has fallen critically low
in database 'tempdb'. All future modifications to this
database will be suspended until the log is successfully
dumped and space becomes available.
The transaction log in database tempdb is almost full. Your
transaction is being suspended until space is made available
in the log.

I have no problem, running queries on Deals_Iq and / or
Deals_Ase separatelly.

Thanks in advance for any hints.

PS. Strange is that, as I can remember, I have been worked
with such union - views between ASE & IQ tables and have
seen no problems.

HenkB

2005-07-30, 7:24 am

When possible try to replace union to union all...

Henk B
<Konstantin> wrote in message news:42ea1c7e.393a.1681692777@sybase.com...
> Hi
>
> Can somebody help me with the following problem:
>
> 1. I have an ASE Table Deals_Ase and an ASE proxy table
> Deals_Iq wich realtes IQ table OldDeals (wich has the same
> structure as Deals_Ase table and contanis archived deals)
>
> 2. I have also a view Deals_View :
>
> create view Deals_View
> as
> select * from Deals_Iq
> union
> select * from Deals_Ase
>
> My problem is that queries on Deals_View like f.e.
>
> select reportDate, count(*) from Deals_View group by
> reportDate
>
> becomes frozen with the error message:
>
> Space available in the log segment has fallen critically low
> in database 'tempdb'. All future modifications to this
> database will be suspended until the log is successfully
> dumped and space becomes available.
> The transaction log in database tempdb is almost full. Your
> transaction is being suspended until space is made available
> in the log.
>
>
> I have no problem, running queries on Deals_Iq and / or
> Deals_Ase separatelly.
>
> Thanks in advance for any hints.
>
> PS. Strange is that, as I can remember, I have been worked
> with such union - views between ASE & IQ tables and have
> seen no problems.

Konstantin

2005-08-01, 7:26 am

I increased tempdb to 6 Gb and got result. It took more then
10 min:

...
Jul 14 2005 12:00AM 12101
Jul 15 2005 12:00AM 19835
Jul 18 2005 12:00AM 19665

Execution Time 1131.
SQL Server cpu time: 113100 ms. SQL Server elapsed time:
693403 ms.

(165 rows affected)
1>

The same query on ASE table only takes less then one second

Execution Time 10.
SQL Server cpu time: 1000 ms. SQL Server elapsed time: 9186
ms.

and the same query on IQ part (proxy table on from ASE to
IQ)
takes less then 0.1 sec

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 220 ms.

But the query from <ASE Table> UNION ALL <IQ Table> takes
more then 10 min. Do I have a chance to get this query
working fasrer ?

> When possible try to replace union to union all...
>
> Henk B
> <Konstantin> wrote in message
> deals) >
>
>

HenkB

2005-08-04, 8:24 pm

HI Konstantin,

Difference between union and union all is that union will do some sorting a
deletes duplicate rows..So theoretically and also by the new doc on iq 12.6
it stated that union all should be faster...

Difference?
Are u comparing apples with apples?(beside ASE versus IQ) I mean: same cpu
power, disks storage, same cardinality on the tables, the same number of
indexes, almost the same period of query time , same size of tempdb,
database options/parameters etc.. Table/Index fragmentation? Have u try
rebuilding the indexes? Collecting statistics? Have u try explaining the
query

Another difference between IQ and ASE is verctor wise storage and querying.
But what I read is that select * from tableA is less efficient in IQ than
rowbased RDBMS. Maybe u should try to name the columns instead of select *
....

Many times I hear people say that the same query run faster in system A than
in system B but after close investigation the systems or the environments
differs..

Success,

Henk B
<Konstantin> wrote in message news:42ee0ee3.1a51.1681692777@sybase.com...[color=darkred]
>I increased tempdb to 6 Gb and got result. It took more then
> 10 min:
>
> ...
> Jul 14 2005 12:00AM 12101
> Jul 15 2005 12:00AM 19835
> Jul 18 2005 12:00AM 19665
>
> Execution Time 1131.
> SQL Server cpu time: 113100 ms. SQL Server elapsed time:
> 693403 ms.
>
> (165 rows affected)
> 1>
>
> The same query on ASE table only takes less then one second
>
> Execution Time 10.
> SQL Server cpu time: 1000 ms. SQL Server elapsed time: 9186
> ms.
>
> and the same query on IQ part (proxy table on from ASE to
> IQ)
> takes less then 0.1 sec
>
> Execution Time 0.
> SQL Server cpu time: 0 ms. SQL Server elapsed time: 220 ms.
>
> But the query from <ASE Table> UNION ALL <IQ Table> takes
> more then 10 min. Do I have a chance to get this query
> working fasrer ?
>
>
>

Sponsored Links