Pomost za SQL
4 отговора
90 прегледа
Pomost za SQL
Poluchih slednoto zapitvae, no ponege SQL-a ne mi e nay-silnata strana se obrastam kam vas s molba za pomost. Nyama da go prevegdam, ne e osbeno trudno, no ne iskam da se gubi nesto pri prevoda. Eto go i samoto zapitvane:
I'm creating a report that will show the customer, order #, the
expected completion date, the foil and the style. I'm using Delphi 4
CS with Paradox and a TQuery. My tables are:
Customer Primary index customer_id
Orders Primary index customer_id and order_id
OrderDetail Primary index customer_id and order_id
Foil Primary index foil_id
Style Primary index style_id
The Style_Name has to meet the criteria that the 1st two characters
in the name are DM.
Additionally, the user wants to use a date range for the Expected
Completion Date.
Here is my SQL statement:
Select c.customer_name,
o.order_id,
o.expected_completion_date,
s.style_name,
f.foil_name
From customer c,
orders o,
style s,
foil f,
orderdetail d
Where o.customer_name = c.customer_name
and d.customer_name = o.customer_name
and o.expected_completion_name between :beg_date and :end_date
and substring(s.style_name from 1 for 2) = 'DM;
Order by s.style_name,
o.expected_completion_date,
o.order_id
Why does it take 1 1/2 hours to run this query? Orders had 17,000
records, orderdetail has 82,000 records, style has 385 records.
Blagodarya predvaritelno!
I'm creating a report that will show the customer, order #, the
expected completion date, the foil and the style. I'm using Delphi 4
CS with Paradox and a TQuery. My tables are:
Customer Primary index customer_id
Orders Primary index customer_id and order_id
OrderDetail Primary index customer_id and order_id
Foil Primary index foil_id
Style Primary index style_id
The Style_Name has to meet the criteria that the 1st two characters
in the name are DM.
Additionally, the user wants to use a date range for the Expected
Completion Date.
Here is my SQL statement:
Select c.customer_name,
o.order_id,
o.expected_completion_date,
s.style_name,
f.foil_name
From customer c,
orders o,
style s,
foil f,
orderdetail d
Where o.customer_name = c.customer_name
and d.customer_name = o.customer_name
and o.expected_completion_name between :beg_date and :end_date
and substring(s.style_name from 1 for 2) = 'DM;
Order by s.style_name,
o.expected_completion_date,
o.order_id
Why does it take 1 1/2 hours to run this query? Orders had 17,000
records, orderdetail has 82,000 records, style has 385 records.
Blagodarya predvaritelno!
в отговор на as_da_da_anon
#2
A-A-A-A!!!!
ами да, нормално, така си е проектирал базата - прави сравнения в where-клаузата по текстови полета, долу даже и някакъв субстринг се наблюдва - да си направи базата по-човешка! не можеш да имаш 17000 фактури и да си такъв DB-профан! сигурно и пари му плащат... както и да е - да си сложи навсякъде ID-та - например, вместо CustomerName - Customer_ID в Order-ите и т.н. А конкретно за тоя SQL-нещо не виждам връзка между таблицата style и останалите - тва прави SQL-а горе-долу безмислен, и 385 пъти по-дълъг (ако има 385 записа в нея). Тоя критерии, за 'DM' не му върши работа самостоятелно.
в отговор на as_da_da_anon
#4
Отг: A-A-A-A!!!!
Kromid e psolutno prav za strukturata na DB-to.
Kolkoto do:
and substring(s.style_name from 1 for 2) = 'DM';
opitai:
and (s.style_name >= 'DM') and (s.style_name < 'DN');
nesam napalno siguren, no tova triabva da e po-barzo.
Kolkoto do:
and substring(s.style_name from 1 for 2) = 'DM';
opitai:
and (s.style_name >= 'DM') and (s.style_name < 'DN');
nesam napalno siguren, no tova triabva da e po-barzo.
в отговор на as_da_da_anon
#5
Отг: Pomost za SQL
Kato nachalo:
tova pravi FULL JOIN s tablica style s i foil f - t.e. vuv where niama s.neshto = , nito f.neshto =..
count(select <(bez style i foil>) * count(foil) * count(style)
Bez tova da nosi NIKAKVA informacia. Vseki red ot ednata, vseki ot drugata.... :)
Shvashtash li maloumieto?
Malko po uchebnik:
1. Indexirai poletata po koito pravish join
2. Join po textovo pole vinagi e po-bavno ot join po int (=, <,> sa po-bavni za stringove)
3. where ..... substring().. ne e dobra e bavno.
4. AMA MNOGO VAZHNO: Ako suibstring() oznachava che v tova pole ima sustavna informacia - tova e generalna greshka. NIKOGA ne biva da se izpolzvat v DB poleta v stil EGN (1-vite 6 cifri., poslednite 4)
Obobshtenie: (ref. Kromid) Celia design na db ochevidno e negramotno napraven.
tova pravi FULL JOIN s tablica style s i foil f - t.e. vuv where niama s.neshto = , nito f.neshto =..
count(select <(bez style i foil>) * count(foil) * count(style)
Bez tova da nosi NIKAKVA informacia. Vseki red ot ednata, vseki ot drugata.... :)
Shvashtash li maloumieto?
Malko po uchebnik:
1. Indexirai poletata po koito pravish join
2. Join po textovo pole vinagi e po-bavno ot join po int (=, <,> sa po-bavni za stringove)
3. where ..... substring().. ne e dobra e bavno.
4. AMA MNOGO VAZHNO: Ako suibstring() oznachava che v tova pole ima sustavna informacia - tova e generalna greshka. NIKOGA ne biva da se izpolzvat v DB poleta v stil EGN (1-vite 6 cifri., poslednite 4)
Obobshtenie: (ref. Kromid) Celia design na db ochevidno e negramotno napraven.