Re: Query Help
From: Mario Splivalo (majk_at_fly.srk.fer.hr)
Date: 08/27/04
- Next message: Patty O'Dors: "RE: QA feels like DOS - What else is out there?"
- Previous message: Diego F.: "Re: Identity primary key"
- In reply to: Hugo Kornelis: "Re: Query Help"
- Next in thread: Hugo Kornelis: "Re: Query Help"
- Reply: Hugo Kornelis: "Re: Query Help"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 27 Aug 2004 09:54:55 +0000 (UTC)
On 2004-08-26, Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote:
> On Wed, 25 Aug 2004 07:02:02 +0000 (UTC), Mario Splivalo wrote:
>
>>The DDL for the tables doc_ir (where the invoices are) and ev_plac (where
>>the payements are) are at the end of this post.
>>
>>I might put the table derived as i2, or i2 (they're the same) in a view, but
>>since this script is rarely used, I didn't want to clog the database with
>>'unnecesary' views.
>
> Hi Mike,
>
> I don't understand this. SQL Server allows you a total of 2,147,483,647
> objects in any database. Creating this view would take only one of these
> available places. Are you really saying that you already have so many
> objects that you can't allow to create some more?
I guess you're right here. But, since that view is used only and only when
that parcitular script is executed, I somehow tought that it shouln't put it
there.
>
> However, I don't think you need to repeat the complete subquery in this
> case. Unless I misunderstand your tables and query, you could simplify
> this to (with apologies for the wrapped lines):
>
> select
> *
> ,
> iznos_racuna - isnull(
> (select sum(ev_plac.ulaz)
> from ev_plac
> where ev_plac.br_rac = i1.br_rac
> and ev_plac.dat_plac <= i1.datum_uplate), 0) AS saldo
> from
> (
> select
> obv_naziv as kupac,
> doc_ir.doc_id as br_rac,
> doc_ir.datum as dat_rac,
> odg_dana as odgoda,
> dat_dosp as dat_dosp,
> uk_vpv3 as iznos_racuna,
> dat_plac as datum_uplate,
> ulaz as iznos_uplate,
> case
> when datediff(dd,dat_dosp, dat_plac) > 0
> then datediff(dd,dat_dosp, dat_plac)
> else 0
> end as kasni_dana
> from
> doc_ir
> left join ev_plac on doc_ir.doc_id =
> ev_plac.br_rac
> where
> left(doc_ir.doc_id, 3) in ('MR1', 'IRR')
>
> ) i1
> order by
> dat_rac, br_rac, datum_uplate
>
> I tested this against empty tables (created with the DDL you provided) and
> it runs fine; of course it doesn't return any rows.
This works fine, thnx!
>
>>Now I'm wondering
>>on how good this query actually is, since I have redundant definition of
>>tables (i1 and i2); is this how it's done, or?
>
> In similar cases, I have seen execution plans where the subquery gets
> executed twice. I must add that I never had the chance to test this kind
> of query against really large databases; it might be possible that in my
> chance, the optimzier decided the plan was "good enough" and didn't look
> further; it might be possible that the optimizer would have created a plan
> where the subquery gets executed only once if there is an incentive (like:
> lots of data, estimated query cost very high) to look further.
I created a data model for keepeng a stock using the FIFO method, in wich I
instantly know the 'out' prices ( no need for the reconciliation of the
records). But, to extract the data from the tables i relay heavy on views.
But, for some particular querries it turns out to be MUCH faster to do the
SELECT * INTO #tempTbl FROM vw_someView1, etc... and then do the joins and
whatever on the #tempTBL and stuff. On the data set that contains like 30k
rows in master table and around 500k rows in the details table the speed
improvements are: from around 2 minutes down to 15 seconds.
Mike
-- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo msplival@jagor.srce.hr
- Next message: Patty O'Dors: "RE: QA feels like DOS - What else is out there?"
- Previous message: Diego F.: "Re: Identity primary key"
- In reply to: Hugo Kornelis: "Re: Query Help"
- Next in thread: Hugo Kornelis: "Re: Query Help"
- Reply: Hugo Kornelis: "Re: Query Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
