Re: [firebird-support] Re: Need Speed Optimization for SQL
@Rob: Ya, this field is already a primary key in the same table and there a
separate index created on this single column.
On Thursday, 16 April 2015 9:58 AM, Virgo Pärna virgo.pa...@mail.ee
[firebird-support] firebird-support@yahoogroups.com wrote:
On Wed, 15 Apr 2015 21:54:32 + (UTC), Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com
wrote:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
I would start by rewriting IN as a JOIN - something like:
SELECT Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM CRM_DOCUMENT_ITEMS JOIN CRM_DOCUMENT_HEADER ON
CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER = CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
JOIN CRM_JOB_ITEMS ON CRM_DOCUMENT_ITEMS.FK_JOB_ITEM =
CRM_JOB_ITEMS.PK_JOB_ITEMS
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and
CRM_JOB_ITEMS.FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}'
--
Virgo Pärna
virgo.pa...@mail.ee
#yiv0919566610 #yiv0919566610 -- #yiv0919566610ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0919566610
#yiv0919566610ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0919566610
#yiv0919566610ygrp-mkp #yiv0919566610hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0919566610 #yiv0919566610ygrp-mkp #yiv0919566610ads
{margin-bottom:10px;}#yiv0919566610 #yiv0919566610ygrp-mkp .yiv0919566610ad
{padding:0 0;}#yiv0919566610 #yiv0919566610ygrp-mkp .yiv0919566610ad p
{margin:0;}#yiv0919566610 #yiv0919566610ygrp-mkp .yiv0919566610ad a
{color:#ff;text-decoration:none;}#yiv0919566610 #yiv0919566610ygrp-sponsor
#yiv0919566610ygrp-lc {font-family:Arial;}#yiv0919566610
#yiv0919566610ygrp-sponsor #yiv0919566610ygrp-lc #yiv0919566610hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0919566610
#yiv0919566610ygrp-sponsor #yiv0919566610ygrp-lc .yiv0919566610ad
{margin-bottom:10px;padding:0 0;}#yiv0919566610 #yiv0919566610actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0919566610
#yiv0919566610activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0919566610
#yiv0919566610activity span {font-weight:700;}#yiv0919566610
#yiv0919566610activity span:first-child
{text-transform:uppercase;}#yiv0919566610 #yiv0919566610activity span a
{color:#5085b6;text-decoration:none;}#yiv0919566610 #yiv0919566610activity span
span {color:#ff7900;}#yiv0919566610 #yiv0919566610activity span
.yiv0919566610underline {text-decoration:underline;}#yiv0919566610
.yiv0919566610attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0919566610 .yiv0919566610attach div a
{text-decoration:none;}#yiv0919566610 .yiv0919566610attach img
{border:none;padding-right:5px;}#yiv0919566610 .yiv0919566610attach label
{display:block;margin-bottom:5px;}#yiv0919566610 .yiv0919566610attach label a
{text-decoration:none;}#yiv0919566610 blockquote {margin:0 0 0
4px;}#yiv0919566610 .yiv0919566610bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0919566610
.yiv0919566610bold a {text-decoration:none;}#yiv0919566610 dd.yiv0919566610last
p a {font-family:Verdana;font-weight:700;}#yiv0919566610 dd.yiv0919566610last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0919566610
dd.yiv0919566610last p span.yiv0919566610yshortcuts
{margin-right:0;}#yiv0919566610 div.yiv0919566610attach-table div div a
{text-decoration:none;}#yiv0919566610 div.yiv0919566610attach-table
{width:400px;}#yiv0919566610 div.yiv0919566610file-title a, #yiv0919566610
div.yiv0919566610file-title a:active, #yiv0919566610
div.yiv0919566610file-title a:hover, #yiv0919566610 div.yiv0919566610file-title
a:visited {text-decoration:none;}#yiv0919566610 div.yiv0919566610photo-title a,
#yiv0919566610 div.yiv0919566610photo-title a:active, #yiv0919566610
div.yiv0919566610photo-title a:hover, #yiv0919566610
div.yiv0919566610photo-title a:visited {text-decoration:none;}#yiv0919566610
div#yiv0919566610ygrp-mlmsg #yiv0919566610ygrp-msg p a
span.yiv0919566610yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0919566610
.yiv0919566610green {color:#628c2a;}#yiv0919566610 .yiv0919566610MsoNormal
{margin:0 0 0 0;}#yiv0919566610 o {font-size:0;}#yiv0919566610
#yiv0919566610photos div {float:left;width:72px;}#yiv0919566610
#yiv0919566610photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv0919566610
#yiv0919566610photos div label
Re: [firebird-support] IBReplicator way to force conflicts
At 06:45 a.m. 16/04/2015, Rodrigo Cardoso rcard...@datasystemnet.com.br [firebird-support wrote: Hi everybody! Are there a way to force all conflicts programmatically? Rodrigo, This is the support list for the Firebird RDBMS. For IBPReplicator questions, you need to subscribe to the ibpreplicator list: mailto:ibp_replicator-requ...@lists.ibphoenix.com?subject=subscribe ^heLen
Re: [firebird-support] Re: Need Speed Optimization for SQL
@Virgo Pärna: No speed improvement.
On Thursday, 16 April 2015 12:15 PM, Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com
wrote:
@Rob: Ya, this field is already a primary key in the same table and there
a separate index created on this single column.
On Thursday, 16 April 2015 9:58 AM, Virgo Pärna virgo.pa...@mail.ee
[firebird-support] firebird-support@yahoogroups.com wrote:
On Wed, 15 Apr 2015 21:54:32 + (UTC), Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com
wrote:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
I would start by rewriting IN as a JOIN - something like:
SELECT Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM CRM_DOCUMENT_ITEMS JOIN CRM_DOCUMENT_HEADER ON
CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER = CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
JOIN CRM_JOB_ITEMS ON CRM_DOCUMENT_ITEMS.FK_JOB_ITEM =
CRM_JOB_ITEMS.PK_JOB_ITEMS
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and
CRM_JOB_ITEMS.FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}'
--
Virgo Pärna
virgo.pa...@mail.ee
#yiv0027696969 #yiv0027696969 -- #yiv0027696969ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0027696969
#yiv0027696969ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0027696969
#yiv0027696969ygrp-mkp #yiv0027696969hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0027696969 #yiv0027696969ygrp-mkp #yiv0027696969ads
{margin-bottom:10px;}#yiv0027696969 #yiv0027696969ygrp-mkp .yiv0027696969ad
{padding:0 0;}#yiv0027696969 #yiv0027696969ygrp-mkp .yiv0027696969ad p
{margin:0;}#yiv0027696969 #yiv0027696969ygrp-mkp .yiv0027696969ad a
{color:#ff;text-decoration:none;}#yiv0027696969 #yiv0027696969ygrp-sponsor
#yiv0027696969ygrp-lc {font-family:Arial;}#yiv0027696969
#yiv0027696969ygrp-sponsor #yiv0027696969ygrp-lc #yiv0027696969hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0027696969
#yiv0027696969ygrp-sponsor #yiv0027696969ygrp-lc .yiv0027696969ad
{margin-bottom:10px;padding:0 0;}#yiv0027696969 #yiv0027696969actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0027696969
#yiv0027696969activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0027696969
#yiv0027696969activity span {font-weight:700;}#yiv0027696969
#yiv0027696969activity span:first-child
{text-transform:uppercase;}#yiv0027696969 #yiv0027696969activity span a
{color:#5085b6;text-decoration:none;}#yiv0027696969 #yiv0027696969activity span
span {color:#ff7900;}#yiv0027696969 #yiv0027696969activity span
.yiv0027696969underline {text-decoration:underline;}#yiv0027696969
.yiv0027696969attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0027696969 .yiv0027696969attach div a
{text-decoration:none;}#yiv0027696969 .yiv0027696969attach img
{border:none;padding-right:5px;}#yiv0027696969 .yiv0027696969attach label
{display:block;margin-bottom:5px;}#yiv0027696969 .yiv0027696969attach label a
{text-decoration:none;}#yiv0027696969 blockquote {margin:0 0 0
4px;}#yiv0027696969 .yiv0027696969bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0027696969
.yiv0027696969bold a {text-decoration:none;}#yiv0027696969 dd.yiv0027696969last
p a {font-family:Verdana;font-weight:700;}#yiv0027696969 dd.yiv0027696969last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0027696969
dd.yiv0027696969last p span.yiv0027696969yshortcuts
{margin-right:0;}#yiv0027696969 div.yiv0027696969attach-table div div a
{text-decoration:none;}#yiv0027696969 div.yiv0027696969attach-table
{width:400px;}#yiv0027696969 div.yiv0027696969file-title a, #yiv0027696969
div.yiv0027696969file-title a:active, #yiv0027696969
div.yiv0027696969file-title a:hover, #yiv0027696969 div.yiv0027696969file-title
a:visited {text-decoration:none;}#yiv0027696969 div.yiv0027696969photo-title a,
#yiv0027696969 div.yiv0027696969photo-title a:active, #yiv0027696969
div.yiv0027696969photo-title a:hover, #yiv0027696969
div.yiv0027696969photo-title a:visited {text-decoration:none;}#yiv0027696969
div#yiv0027696969ygrp-mlmsg #yiv0027696969ygrp-msg p a
span.yiv0027696969yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0027696969
.yiv0027696969green {color:#628c2a;}#yiv0027696969 .yiv0027696969MsoNormal
{margin:0 0 0 0;}#yiv0027696969 o {font-size:0;}#yiv0027696969
Re: [firebird-support] Need Speed Optimization for SQL
Hi SET,
I tried as below way by adding Group By clause:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
And sql is taking 1.893 seconds
Wwww SET !! You again saved dear friend..
Hats Off to You as Usual My Dear.
Any other way to make this more faster ? Please If possible
On Thursday, 16 April 2015 12:54 PM, Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com
wrote:
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i
think group by clause is needed, would you please help?
Error:
Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)
On Thursday, 16 April 2015 12:48 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support]
firebird-support@yahoogroups.com wrote:
I have below SQL and DDL of respective columns used in this SQL.
Below sql takes 6-7 seconds to give the result.
Would it be possible to bring the output time to 1 seconds, coz there are some
other operartion I need to perform based on this sql output withing short
period of t ime.
SQL:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
Hopefully, Rob or Virgos solutions worked, if not try this:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
HTH,
Set
#yiv7270778608 #yiv7270778608 -- #yiv7270778608ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7270778608
#yiv7270778608ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7270778608
#yiv7270778608ygrp-mkp #yiv7270778608hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7270778608 #yiv7270778608ygrp-mkp #yiv7270778608ads
{margin-bottom:10px;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad
{padding:0 0;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad p
{margin:0;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad a
{color:#ff;text-decoration:none;}#yiv7270778608 #yiv7270778608ygrp-sponsor
#yiv7270778608ygrp-lc {font-family:Arial;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ygrp-lc #yiv7270778608hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ygrp-lc .yiv7270778608ad
{margin-bottom:10px;padding:0 0;}#yiv7270778608 #yiv7270778608actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7270778608
#yiv7270778608activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7270778608
#yiv7270778608activity span {font-weight:700;}#yiv7270778608
#yiv7270778608activity span:first-child
{text-transform:uppercase;}#yiv7270778608 #yiv7270778608activity span a
{color:#5085b6;text-decoration:none;}#yiv7270778608 #yiv7270778608activity span
span {color:#ff7900;}#yiv7270778608 #yiv7270778608activity span
.yiv7270778608underline {text-decoration:underline;}#yiv7270778608
.yiv7270778608attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7270778608 .yiv7270778608attach div a
{text-decoration:none;}#yiv7270778608 .yiv7270778608attach img
{border:none;padding-right:5px;}#yiv7270778608 .yiv7270778608attach label
{display:block;margin-bottom:5px;}#yiv7270778608 .yiv7270778608attach label a
{text-decoration:none;}#yiv7270778608 blockquote {margin:0 0 0
4px;}#yiv7270778608 .yiv7270778608bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7270778608
.yiv7270778608bold a {text-decoration:none;}#yiv7270778608 dd.yiv7270778608last
p a {font-family:Verdana;font-weight:700;}#yiv7270778608 dd.yiv7270778608last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7270778608
dd.yiv7270778608last p span.yiv7270778608yshortcuts
{margin-right:0;}#yiv7270778608 div.yiv7270778608attach-table div div a
RE: [firebird-support] Need Speed Optimization for SQL
I have below SQL and DDL of respective columns used in this SQL.
Below sql takes 6-7 seconds to give the result.
Would it be possible to bring the output time to 1 seconds, coz there are some
other operartion I need to perform based on this sql output withing short
period of t ime.
SQL:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
Hopefully, Rob or Virgos solutions worked, if not try this:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
HTH,
Set
Re: [firebird-support] Need Speed Optimization for SQL
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i
think group by clause is needed, would you please help?
Error:
Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)
On Thursday, 16 April 2015 12:48 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support]
firebird-support@yahoogroups.com wrote:
I have below SQL and DDL of respective columns used in this SQL.
Below sql takes 6-7 seconds to give the result.
Would it be possible to bring the output time to 1 seconds, coz there are some
other operartion I need to perform based on this sql output withing short
period of t ime.
SQL:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
Hopefully, Rob or Virgos solutions worked, if not try this:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
HTH,
Set
#yiv4888185622 #yiv4888185622 -- #yiv4888185622ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4888185622
#yiv4888185622ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4888185622
#yiv4888185622ygrp-mkp #yiv4888185622hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv4888185622 #yiv4888185622ygrp-mkp #yiv4888185622ads
{margin-bottom:10px;}#yiv4888185622 #yiv4888185622ygrp-mkp .yiv4888185622ad
{padding:0 0;}#yiv4888185622 #yiv4888185622ygrp-mkp .yiv4888185622ad p
{margin:0;}#yiv4888185622 #yiv4888185622ygrp-mkp .yiv4888185622ad a
{color:#ff;text-decoration:none;}#yiv4888185622 #yiv4888185622ygrp-sponsor
#yiv4888185622ygrp-lc {font-family:Arial;}#yiv4888185622
#yiv4888185622ygrp-sponsor #yiv4888185622ygrp-lc #yiv4888185622hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4888185622
#yiv4888185622ygrp-sponsor #yiv4888185622ygrp-lc .yiv4888185622ad
{margin-bottom:10px;padding:0 0;}#yiv4888185622 #yiv4888185622actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4888185622
#yiv4888185622activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4888185622
#yiv4888185622activity span {font-weight:700;}#yiv4888185622
#yiv4888185622activity span:first-child
{text-transform:uppercase;}#yiv4888185622 #yiv4888185622activity span a
{color:#5085b6;text-decoration:none;}#yiv4888185622 #yiv4888185622activity span
span {color:#ff7900;}#yiv4888185622 #yiv4888185622activity span
.yiv4888185622underline {text-decoration:underline;}#yiv4888185622
.yiv4888185622attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv4888185622 .yiv4888185622attach div a
{text-decoration:none;}#yiv4888185622 .yiv4888185622attach img
{border:none;padding-right:5px;}#yiv4888185622 .yiv4888185622attach label
{display:block;margin-bottom:5px;}#yiv4888185622 .yiv4888185622attach label a
{text-decoration:none;}#yiv4888185622 blockquote {margin:0 0 0
4px;}#yiv4888185622 .yiv4888185622bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv4888185622
.yiv4888185622bold a {text-decoration:none;}#yiv4888185622 dd.yiv4888185622last
p a {font-family:Verdana;font-weight:700;}#yiv4888185622 dd.yiv4888185622last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4888185622
dd.yiv4888185622last p span.yiv4888185622yshortcuts
{margin-right:0;}#yiv4888185622 div.yiv4888185622attach-table div div a
{text-decoration:none;}#yiv4888185622 div.yiv4888185622attach-table
{width:400px;}#yiv4888185622 div.yiv4888185622file-title a, #yiv4888185622
div.yiv4888185622file-title a:active, #yiv4888185622
div.yiv4888185622file-title a:hover, #yiv4888185622 div.yiv4888185622file-title
a:visited {text-decoration:none;}#yiv4888185622 div.yiv4888185622photo-title a,
#yiv4888185622 div.yiv4888185622photo-title a:active, #yiv4888185622
div.yiv4888185622photo-title a:hover, #yiv4888185622
div.yiv4888185622photo-title a:visited {text-decoration:none;}#yiv4888185622
div#yiv4888185622ygrp-mlmsg #yiv4888185622ygrp-msg p a
span.yiv4888185622yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4888185622
.yiv4888185622green {color:#628c2a;}#yiv4888185622 .yiv4888185622MsoNormal
{margin:0 0 0 0;}#yiv4888185622 o
RE: [firebird-support] Need Speed Optimization for SQL
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i
think group by clause is needed, would you please help?
Yes, sorry I forgot group by:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
HTH,
Set
RE: [firebird-support] Need Speed Optimization for SQL
Hi SET,
I tried as below way by adding Group By clause:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
And sql is taking 1.893 seconds
Any other way to make this more faster ? Please If possible
If you have indexes (or if they are keys) for these three fields:
CRM_JOB_ITEMS.FK_JOB
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
then it will most likely be difficult to optimize this query any further
(sometimes having trigger generated summary tables can help, but that does
complicate things quite a bit and I've never seen it done when several tables
are involved, so I'd only consider this if this particular query was executed
frequently and was one of the most important queries in your system).
Note that while I may be considered a Firebird SELECT expert, I am a novice
regarding other parts of Firebird (like configuration settings), so maybe
someone else can help you speed up your query a bit more.
Set
[firebird-support] Re: Need Speed Optimization for SQL
CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having seperate index on this column as well What do you mean by this, Vishal? I think it was fixed a while ago, but duplicate indexes used to confuse the optimizer, so PK_DOCUMENT_HEADER ought not to be both the primary key and have a separate single-field index (unless the PK or index is DESCENDING or consists of more than one field) - at best it makes INSERTs marginally slower due to having to maintain two identical indexes. Set
Re: [firebird-support] Re: Plan question, what is a stream and what isn't?
On 15/04/2015 18:50, hv...@users.sourceforge.net [firebird-support] wrote: ---In firebird-support@yahoogroups.com, listas@... wrote : Re: [firebird-support] Re: Plan question, what is a stream and what isn't? Note, stored procedures and triggers are prepared\optimized once when loaded into metadata cache. Regards, Vlad Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right before first execution? Exactly. What does right before first execution mean? What is the lifecycle of metadata cache? So what's the answer to my question, re a process which keeps a connection open permanently and repeatedly re-runs the same procedures in different transactions? Does it need to drop and recreate the connection in order to take account of any SET STATISTICS done by other people, or not? -- Tim Ward
Re: [firebird-support] Re: Need Speed Optimization for SQL
I tried dropping that index, but sql execution time is still same i.e. 1.893
seconds.
On Thursday, 16 April 2015 2:15 PM, Vishal Tiwari
vishuals...@yahoo.co.in wrote:
Sorry SET, but this is an existing database, I understand your point and
completely agree with you. Shall I try droping that index then ? What you
suggest ?
On Thursday, 16 April 2015 1:35 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support]
firebird-support@yahoogroups.com wrote:
CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having
seperate index on this column as well
What do you mean by this, Vishal?
I think it was fixed a while ago, but duplicate indexes used to confuse the
optimizer, so PK_DOCUMENT_HEADER ought not to be both the primary key and have
a separate single-field index (unless the PK or index is DESCENDING or consists
of more than one field) - at best it makes INSERTs marginally slower due to
having to maintain two identical indexes.
Set
#yiv9985683921 -- #yiv9985683921ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9985683921
#yiv9985683921ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9985683921
#yiv9985683921ygrp-mkp #yiv9985683921hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9985683921 #yiv9985683921ygrp-mkp #yiv9985683921ads
{margin-bottom:10px;}#yiv9985683921 #yiv9985683921ygrp-mkp .yiv9985683921ad
{padding:0 0;}#yiv9985683921 #yiv9985683921ygrp-mkp .yiv9985683921ad p
{margin:0;}#yiv9985683921 #yiv9985683921ygrp-mkp .yiv9985683921ad a
{color:#ff;text-decoration:none;}#yiv9985683921 #yiv9985683921ygrp-sponsor
#yiv9985683921ygrp-lc {font-family:Arial;}#yiv9985683921
#yiv9985683921ygrp-sponsor #yiv9985683921ygrp-lc #yiv9985683921hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9985683921
#yiv9985683921ygrp-sponsor #yiv9985683921ygrp-lc .yiv9985683921ad
{margin-bottom:10px;padding:0 0;}#yiv9985683921 #yiv9985683921actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9985683921
#yiv9985683921activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9985683921
#yiv9985683921activity span {font-weight:700;}#yiv9985683921
#yiv9985683921activity span:first-child
{text-transform:uppercase;}#yiv9985683921 #yiv9985683921activity span a
{color:#5085b6;text-decoration:none;}#yiv9985683921 #yiv9985683921activity span
span {color:#ff7900;}#yiv9985683921 #yiv9985683921activity span
.yiv9985683921underline {text-decoration:underline;}#yiv9985683921
.yiv9985683921attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9985683921 .yiv9985683921attach div a
{text-decoration:none;}#yiv9985683921 .yiv9985683921attach img
{border:none;padding-right:5px;}#yiv9985683921 .yiv9985683921attach label
{display:block;margin-bottom:5px;}#yiv9985683921 .yiv9985683921attach label a
{text-decoration:none;}#yiv9985683921 blockquote {margin:0 0 0
4px;}#yiv9985683921 .yiv9985683921bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9985683921
.yiv9985683921bold a {text-decoration:none;}#yiv9985683921 dd.yiv9985683921last
p a {font-family:Verdana;font-weight:700;}#yiv9985683921 dd.yiv9985683921last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9985683921
dd.yiv9985683921last p span.yiv9985683921yshortcuts
{margin-right:0;}#yiv9985683921 div.yiv9985683921attach-table div div a
{text-decoration:none;}#yiv9985683921 div.yiv9985683921attach-table
{width:400px;}#yiv9985683921 div.yiv9985683921file-title a, #yiv9985683921
div.yiv9985683921file-title a:active, #yiv9985683921
div.yiv9985683921file-title a:hover, #yiv9985683921 div.yiv9985683921file-title
a:visited {text-decoration:none;}#yiv9985683921 div.yiv9985683921photo-title a,
#yiv9985683921 div.yiv9985683921photo-title a:active, #yiv9985683921
div.yiv9985683921photo-title a:hover, #yiv9985683921
div.yiv9985683921photo-title a:visited {text-decoration:none;}#yiv9985683921
div#yiv9985683921ygrp-mlmsg #yiv9985683921ygrp-msg p a
span.yiv9985683921yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9985683921
.yiv9985683921green {color:#628c2a;}#yiv9985683921 .yiv9985683921MsoNormal
{margin:0 0 0 0;}#yiv9985683921 o {font-size:0;}#yiv9985683921
#yiv9985683921photos div {float:left;width:72px;}#yiv9985683921
#yiv9985683921photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv9985683921
#yiv9985683921photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9985683921
#yiv9985683921reco-category {font-size:77%;}#yiv9985683921
#yiv9985683921reco-desc {font-size:77%;}#yiv9985683921 .yiv9985683921replbq
{margin:4px;}#yiv9985683921 #yiv9985683921ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9985683921
[firebird-support] Re: Need Speed Optimization for SQL
On Thu, 16 Apr 2015 07:10:47 + (UTC), Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com wrote: [-- Type: text/plain, Encoding: quoted-printable --] @Virgo Pärna: No speed improvement. What are the indexes on all three tables? -- Virgo Pärna virgo.pa...@mail.ee
Re: [firebird-support] Re: Need Speed Optimization for SQL
Hi Virgo,
Below are the details:
CRM_JOB_ITEMS Table - Two seperate indexes on independent columns
Index No. 1. FK_JOB,
Index No. 2. PK_JOB_ITEMS
PK_JOB_ITEMS column is primary key and it also has seperate index on this
single column
There are four other indexes as well on independent columns
---
CRM_DOCUMENT_ITEMS Table - No index on FK_JOB_ITEM column which is used for
inner join
Index No. 1 - FK_DOCUMENT_HEADER
There are two other indexes as well on independent columns
---
CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having
seperate index on this column as well
There are two other indexes as well on independent columns, one of them index
is on FK_JOB column as well (single column in its index)
With Best Regards.
Vishal
On Thursday, 16 April 2015 1:06 PM, Virgo Pärna virgo.pa...@mail.ee
[firebird-support] firebird-support@yahoogroups.com wrote:
On Thu, 16 Apr 2015 07:10:47 + (UTC), Vishal Tiwari
vishuals...@yahoo.co.in [firebird-support] firebird-support@yahoogroups.com
wrote:
[-- Type: text/plain, Encoding: quoted-printable --]
@Virgo Pärna: No speed improvement.
What are the indexes on all three tables?
--
Virgo Pärna
virgo.pa...@mail.ee
#yiv2747013542 #yiv2747013542 -- #yiv2747013542ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2747013542
#yiv2747013542ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2747013542
#yiv2747013542ygrp-mkp #yiv2747013542hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv2747013542 #yiv2747013542ygrp-mkp #yiv2747013542ads
{margin-bottom:10px;}#yiv2747013542 #yiv2747013542ygrp-mkp .yiv2747013542ad
{padding:0 0;}#yiv2747013542 #yiv2747013542ygrp-mkp .yiv2747013542ad p
{margin:0;}#yiv2747013542 #yiv2747013542ygrp-mkp .yiv2747013542ad a
{color:#ff;text-decoration:none;}#yiv2747013542 #yiv2747013542ygrp-sponsor
#yiv2747013542ygrp-lc {font-family:Arial;}#yiv2747013542
#yiv2747013542ygrp-sponsor #yiv2747013542ygrp-lc #yiv2747013542hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2747013542
#yiv2747013542ygrp-sponsor #yiv2747013542ygrp-lc .yiv2747013542ad
{margin-bottom:10px;padding:0 0;}#yiv2747013542 #yiv2747013542actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2747013542
#yiv2747013542activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2747013542
#yiv2747013542activity span {font-weight:700;}#yiv2747013542
#yiv2747013542activity span:first-child
{text-transform:uppercase;}#yiv2747013542 #yiv2747013542activity span a
{color:#5085b6;text-decoration:none;}#yiv2747013542 #yiv2747013542activity span
span {color:#ff7900;}#yiv2747013542 #yiv2747013542activity span
.yiv2747013542underline {text-decoration:underline;}#yiv2747013542
.yiv2747013542attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv2747013542 .yiv2747013542attach div a
{text-decoration:none;}#yiv2747013542 .yiv2747013542attach img
{border:none;padding-right:5px;}#yiv2747013542 .yiv2747013542attach label
{display:block;margin-bottom:5px;}#yiv2747013542 .yiv2747013542attach label a
{text-decoration:none;}#yiv2747013542 blockquote {margin:0 0 0
4px;}#yiv2747013542 .yiv2747013542bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2747013542
.yiv2747013542bold a {text-decoration:none;}#yiv2747013542 dd.yiv2747013542last
p a {font-family:Verdana;font-weight:700;}#yiv2747013542 dd.yiv2747013542last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2747013542
dd.yiv2747013542last p span.yiv2747013542yshortcuts
{margin-right:0;}#yiv2747013542 div.yiv2747013542attach-table div div a
{text-decoration:none;}#yiv2747013542 div.yiv2747013542attach-table
{width:400px;}#yiv2747013542 div.yiv2747013542file-title a, #yiv2747013542
div.yiv2747013542file-title a:active, #yiv2747013542
div.yiv2747013542file-title a:hover, #yiv2747013542 div.yiv2747013542file-title
a:visited {text-decoration:none;}#yiv2747013542 div.yiv2747013542photo-title a,
#yiv2747013542 div.yiv2747013542photo-title a:active, #yiv2747013542
div.yiv2747013542photo-title a:hover, #yiv2747013542
div.yiv2747013542photo-title a:visited {text-decoration:none;}#yiv2747013542
div#yiv2747013542ygrp-mlmsg #yiv2747013542ygrp-msg p a
span.yiv2747013542yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2747013542
.yiv2747013542green {color:#628c2a;}#yiv2747013542 .yiv2747013542MsoNormal
{margin:0 0 0 0;}#yiv2747013542 o {font-size:0;}#yiv2747013542
#yiv2747013542photos div {float:left;width:72px;}#yiv2747013542
#yiv2747013542photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv2747013542
#yiv2747013542photos div label
Re: [firebird-support] Re: Need Speed Optimization for SQL
Sorry SET, but this is an existing database, I understand your point and
completely agree with you. Shall I try droping that index then ? What you
suggest ?
On Thursday, 16 April 2015 1:35 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support]
firebird-support@yahoogroups.com wrote:
CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having
seperate index on this column as well
What do you mean by this, Vishal?
I think it was fixed a while ago, but duplicate indexes used to confuse the
optimizer, so PK_DOCUMENT_HEADER ought not to be both the primary key and have
a separate single-field index (unless the PK or index is DESCENDING or consists
of more than one field) - at best it makes INSERTs marginally slower due to
having to maintain two identical indexes.
Set
#yiv8332934632 #yiv8332934632 -- #yiv8332934632ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8332934632
#yiv8332934632ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8332934632
#yiv8332934632ygrp-mkp #yiv8332934632hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8332934632 #yiv8332934632ygrp-mkp #yiv8332934632ads
{margin-bottom:10px;}#yiv8332934632 #yiv8332934632ygrp-mkp .yiv8332934632ad
{padding:0 0;}#yiv8332934632 #yiv8332934632ygrp-mkp .yiv8332934632ad p
{margin:0;}#yiv8332934632 #yiv8332934632ygrp-mkp .yiv8332934632ad a
{color:#ff;text-decoration:none;}#yiv8332934632 #yiv8332934632ygrp-sponsor
#yiv8332934632ygrp-lc {font-family:Arial;}#yiv8332934632
#yiv8332934632ygrp-sponsor #yiv8332934632ygrp-lc #yiv8332934632hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8332934632
#yiv8332934632ygrp-sponsor #yiv8332934632ygrp-lc .yiv8332934632ad
{margin-bottom:10px;padding:0 0;}#yiv8332934632 #yiv8332934632actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8332934632
#yiv8332934632activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8332934632
#yiv8332934632activity span {font-weight:700;}#yiv8332934632
#yiv8332934632activity span:first-child
{text-transform:uppercase;}#yiv8332934632 #yiv8332934632activity span a
{color:#5085b6;text-decoration:none;}#yiv8332934632 #yiv8332934632activity span
span {color:#ff7900;}#yiv8332934632 #yiv8332934632activity span
.yiv8332934632underline {text-decoration:underline;}#yiv8332934632
.yiv8332934632attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8332934632 .yiv8332934632attach div a
{text-decoration:none;}#yiv8332934632 .yiv8332934632attach img
{border:none;padding-right:5px;}#yiv8332934632 .yiv8332934632attach label
{display:block;margin-bottom:5px;}#yiv8332934632 .yiv8332934632attach label a
{text-decoration:none;}#yiv8332934632 blockquote {margin:0 0 0
4px;}#yiv8332934632 .yiv8332934632bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8332934632
.yiv8332934632bold a {text-decoration:none;}#yiv8332934632 dd.yiv8332934632last
p a {font-family:Verdana;font-weight:700;}#yiv8332934632 dd.yiv8332934632last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8332934632
dd.yiv8332934632last p span.yiv8332934632yshortcuts
{margin-right:0;}#yiv8332934632 div.yiv8332934632attach-table div div a
{text-decoration:none;}#yiv8332934632 div.yiv8332934632attach-table
{width:400px;}#yiv8332934632 div.yiv8332934632file-title a, #yiv8332934632
div.yiv8332934632file-title a:active, #yiv8332934632
div.yiv8332934632file-title a:hover, #yiv8332934632 div.yiv8332934632file-title
a:visited {text-decoration:none;}#yiv8332934632 div.yiv8332934632photo-title a,
#yiv8332934632 div.yiv8332934632photo-title a:active, #yiv8332934632
div.yiv8332934632photo-title a:hover, #yiv8332934632
div.yiv8332934632photo-title a:visited {text-decoration:none;}#yiv8332934632
div#yiv8332934632ygrp-mlmsg #yiv8332934632ygrp-msg p a
span.yiv8332934632yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8332934632
.yiv8332934632green {color:#628c2a;}#yiv8332934632 .yiv8332934632MsoNormal
{margin:0 0 0 0;}#yiv8332934632 o {font-size:0;}#yiv8332934632
#yiv8332934632photos div {float:left;width:72px;}#yiv8332934632
#yiv8332934632photos div div {border:1px solid
#66;height:62px;overflow:hidden;width:62px;}#yiv8332934632
#yiv8332934632photos div label
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8332934632
#yiv8332934632reco-category {font-size:77%;}#yiv8332934632
#yiv8332934632reco-desc {font-size:77%;}#yiv8332934632 .yiv8332934632replbq
{margin:4px;}#yiv8332934632 #yiv8332934632ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8332934632 #yiv8332934632ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8332934632
#yiv8332934632ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8332934632
Re: [firebird-support] Need Speed Optimization for SQL
Hi SET,
I tried as below way by adding Group By clause:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER =
cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
And sql is taking 1.893 seconds
Any other way to make this more faster ? Please If possible
If you have indexes (or if they are keys) for these three fields:
CRM_JOB_ITEMS.FK_JOB
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
then it will most likely be difficult to optimize this query any further
(sometimes having trigger generated summary tables can help, but that does
complicate things quite a bit and I've never seen it done when several tables
are involved, so I'd only consider this if this particular query was executed
frequently and was one of the most important queries in your system).
Note that while I may be considered a Firebird SELECT expert, I am a novice
regarding other parts of Firebird (like configuration settings), so maybe
someone else can help you speed up your query a bit more.
Interesting to see that you are able to solve most/all of the query
performance problems reported here by working around using CTEs.
Shouldn't that ring a bell for Firebird developers in the optimizer
area? ;-)
Regarding configuration settings for the thread creator:
* What is the result of gstat -h for the database?
* What exact version and Firebird architecture are you using?
* Number of concurrent connections?
* Available RAM?
* What's the TempCacheLimit entry in firebird.conf?
* What's the FileSystemCacheThreshold entry in firebird.conf?
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] Creating proper index
Hi I have a table that has (among others) two fields: BL_PREFIX VARCHAR10 BOOKING_NO INTEGER Column BL_PREFIX will have two, maximum three values I need that following statement should be as fast as possible: SELECT MAX(BOOKING_NO) FROM BOOKINGS WHERE BL_PREFIX = :BL_PREFIX Is the compound descending index (BL_PREFIX, BOOKING_NO) enough for above query? Thanks Marcin
Re: [firebird-support] Re: Plan question, what is a stream and what isn't?
---In firebird-support@yahoogroups.com, tdw@... wrote : On 15/04/2015 18:50, hvlad@... mailto:hvlad@... [firebird-support] wrote: ---In firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com, listas@... mailto:listas@... wrote : Re: [firebird-support] Re: Plan question, what is a stream and what isn't? Note, stored procedures and triggers are prepared\optimized once when loaded into metadata cache. Regards, Vlad Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right before first execution? Exactly. What does right before first execution mean? Hmm... right before first execution mean *exactly* right before first execution ... what is confusing here ? Application prepare the statement which referenced some stored procedure. Engine must resolve all objects referenced by statement to create execution tree. Engine looks for object in metadata cache and, if not found, reads system catalogue and construct necessary object. It involves creating execution tree for this object, of course. Then ready to use execution tree put into the metadata cache (with other necessary bits of information). So, about right before first execution - probably at first reference is more clear ? What is the lifecycle of metadata cache? Object (stored procedure\trigger) put into metadata cache at first reference. Object could be removed from metadata cache when index or relation (table) is dropped. Object instance is marked as obsolete when user alter corresponding procedure\trigger. In this case new instance of object will be created and put into metadata cache. Old instance will not be used by newly prepared statements. In SS metadata cache is shared by all attachements. In CS\SC each attachment have its own private copy of metadata cache. It is not recommended (by docs, since IB times) to alter objects when there are other attachments present. SET STATISTICS doesn't invalidate any objects in metadata cache. So what's the answer to my question, re a process which keeps a connection open permanently and repeatedly re-runs the same procedures in different transactions? Does it need to drop and recreate the connection in order to take account of any SET STATISTICS done by other people, or not? Hope now you can answer on questions above by yourself :) Regards, Vlad
[firebird-support] Re: Firebird Roadmap: v3.0 - what's next?
16.04.2015 16:56, Michael Schwarze wrote: It is about the general direction Firebird is heading: will it come closer to NoSQL approaches, for instance? It depends on what you mean by closer and what NoSQL features you're interested in. Many aspects of SQL vs NoSQL are orthogonal and I don't think you expect us to rewrite the codebase from scratch. Dmitry
[firebird-support] Uniqe-Problem
Hi, i do have an problem with UNIQUE constraint on FB 2.5.2 Data is in an varchar having Charset win1252 and collation pxw_intl. Insert is done using an prepared statement. Writing Data Amelie Amélie does raise an Unique-Error. Just curious: - Data comes from an Table having the same UNIQUE constraint - When selecting with equality only one of the Variations is found. Any Idea what's going wrong? Elmar
Re: [firebird-support] Firebird Roadmap: v3.0 - what's next?
2015-04-16
Thread
Schwarze Consulting - Michael Schwarze michael.schwa...@schwarze-consulting.de [firebird-support]
Hi Thomas, Perspective is avery good point; I’m sure it’s a major effort for the developers involved in all the upcoming features and I’m looking forward to getting my hands on quite a few of them, for instance Mapping of OS users/groups to DB users/roles. But my question was not about single features. It is about the general direction Firebird is heading: will it come closer to NoSQL approaches, for instance? Is there a strategy beyond the feature driven roadmap for v3 / v4 or will it stay feature driven? Cheers, Michael Am 15.04.2015 um 17:45 schrieb Thomas Steinmaurer t...@iblogmanager.com [firebird-support] firebird-support@yahoogroups.com: …many thanks; that’s what I was looking for! So there’s a great many work under way, but it looks to me that v3 and v4 are currently more or less optimizations / enhancements and no major changes coming up, right? What are major changes from your perspective? Fine-grained SMP with a shared page cache in Firebird 3 is IMHO a major change and long-awaited. And that's just only one new feature. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. Am 15.04.2015 um 09:25 schrieb Gabor Boros gaborbo...@yahoo.com [firebird-support] firebird-support@yahoogroups.com: 2015.04.15. 8:58 keltezéssel, Schwarze Consulting - Michael Schwarze michael.schwa...@schwarze-consulting.de [firebird-support] írta: Hi, Just out of curiosity: what’s coming after Firebird v3.0? I checked the upcoming roadmap [1] and was wondering whether there might be any NoSQL ideas, etc. coming with a potential v4.0? Regards, Michael Hi, http://www.firebirdsql.org/file/community/conference-2014/pdf/01_firebird_devplanning.pdf Gabor Posted by: Schwarze Consulting - Michael Schwarze michael.schwa...@schwarze-consulting.de ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
Re: [firebird-support] Firebird Roadmap: v3.0 - what's next?
2015-04-16
Thread
Schwarze Consulting - Michael Schwarze michael.schwa...@schwarze-consulting.de [firebird-support]
Hi Dmitry, Thanks for your reply. I’m not expecting anything here; the NoSQL-thing was just meant to be an example for what might be a major shift to me. I was just curious what will be coming after v3 and got a good idea of the features which might come with v4 now after following the links in this thread. What’s left open to me: are there already any ideas (a strategy) beyond the roadmap’s features like anything the firebird development team always wanted the software to be? Greetings, Michael Am 16.04.2015 um 17:07 schrieb Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] firebird-support@yahoogroups.com: 16.04.2015 16:56, Michael Schwarze wrote: It is about the general direction Firebird is heading: will it come closer to NoSQL approaches, for instance? It depends on what you mean by closer and what NoSQL features you're interested in. Many aspects of SQL vs NoSQL are orthogonal and I don't think you expect us to rewrite the codebase from scratch. Dmitry
