Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Re: [firebird-support] Cache Performance Options
Hi Karol, what Windows 2008 memory problem are You refering to that needs 2.5.3? Thanks Zsazsi 2014-05-13 7:23 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com: Hi, Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern: Dbpage size * dbbuffers Or if dbbuffer is 0 Db page size * defaultdbcachepages You must add to this sort buffer for any connection. You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem Regards, Karol Bieniasxewski Wysłane z mojego HTC - Reply message - Od: Joshua Hartmann jhartm...@computekdental.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: pon., maj 12, 2014 21:32 I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04. Xeon 1275 4 cores allocated to the virtual server 12GB memory allocated to the virtual server (32GB total on the host machine) 750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable) The database is ~30GB At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server. We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes. I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB. So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference. Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast. I guess my questions are: In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%. Should I enable or disable async? Should I enable auto sweeping or not? Would performance be faster if we upgraded to the 64bit version of 2.5.2? Thanks for any advice you can give. -Josh
Odp: [firebird-support] Cache Performance Options
Hi, Write in google firebird and windows 2008 cache problem and you will see Regards, Karol Bieniaszewski - Reply message - Od: Zsazsi m-zs...@freemail.hu [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: wt., maj 13, 2014 08:44 Hi Karol, what Windows 2008 memory problem are You refering to that needs 2.5.3? Thanks Zsazsi 2014-05-13 7:23 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com: Hi, Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern: Dbpage size * dbbuffers Or if dbbuffer is 0 Db page size * defaultdbcachepages You must add to this sort buffer for any connection. You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem Regards, Karol Bieniasxewski Wysłane z mojego HTC - Reply message - Od: Joshua Hartmann jhartm...@computekdental.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: pon., maj 12, 2014 21:32 I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04.Xeon 12754 cores allocated to the virtual server12GB memory allocated to the virtual server (32GB total on the host machine) 750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable) The database is ~30GB At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server. We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes. I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB. So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference. Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast. I guess my questions are: In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%.Should I enable or disable async? Should I enable auto sweeping or not?Would performance be faster if we upgraded to the 64bit version of 2.5.2? Thanks for any advice you can give. -Josh
RE: [firebird-support] COALESCE() + CAST() converts timestamp format
By the way, Bhavbhuti, CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt will fail if tPB.dBillDt is null since '' cannot be converted to a timestamp (it is neither a timestamp nor null). Set
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
RE: [firebird-support] COALESCE() + CAST() converts timestamp format
Thanks Svein. Would you suggest I double COALESCE it? I think it will be an overkill and I might end up with an ANSI datetime again. Or should I not COALESCE it at all? I wanted a blank date time in case of a NULL for tBillDt and if there is a date in tBillDt I need it for date calculation later on as post processing from the front ends. What is a blank date, Bhavbhuti? Either it is NULL (unknown) or an actual date. Blank as in '' is a (var)char concept, it doesn't exist for dates or timestamps and give an error. So you basically have to choose between having a date for calculation or a string for display. If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or . HTH, Set
Re: [firebird-support] Cache Performance Options
Hi Karol, I can see nothing related, The only problem that shows up was fixed in 2.5.2 already. http://tracker.firebirdsql.org/browse/CORE-3791 Do You know something more specific? Regards Zsazsi 2014-05-13 8:56 GMT+02:00 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com: Hi, Write in google firebird and windows 2008 cache problem and you will see Regards, Karol Bieniaszewski - Reply message - Od: Zsazsi m-zs...@freemail.hu [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: wt., maj 13, 2014 08:44 Hi Karol, what Windows 2008 memory problem are You refering to that needs 2.5.3? Thanks Zsazsi
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Svein, Bhavbhuti has a background as a Visual FoxPro developer and in that language exist empty dates. Probably is the reason why he had casted an empty string as a timestamp. The same thing had happened to me when I was learning SQL. Greetings. Walter. On Tue, May 13, 2014 at 4:02 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: Thanks Svein. Would you suggest I double COALESCE it? I think it will be an overkill and I might end up with an ANSI datetime again. Or should I not COALESCE it at all? I wanted a blank date time in case of a NULL for tBillDt and if there is a date in tBillDt I need it for date calculation later on as post processing from the front ends. What is a blank date, Bhavbhuti? Either it is NULL (unknown) or an actual date. Blank as in '' is a (var)char concept, it doesn't exist for dates or timestamps and give an error. So you basically have to choose between having a date for calculation or a string for display. If you're thinking in terms of WHERE clauses, you may sometimes use IS [NOT] DISTINCT FROM as an alternative to = or . HTH, Set
Re: [firebird-support] Cache Performance Options
Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
RES: [firebird-support] Cache Performance Options
Large gap is not caused by lack of auto sweep. It is bad system design. As you is not the system programmer, you can shut down all connections to the database during lunch time, then you can let users enter in your system again. After shutting down, run a manual sweep (gfix –sweep). If you see a increase in performance, your system is bad transaction designed. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: terça-feira, 13 de maio de 2014 11:31 Para: firebird-support@yahoogroups.com Assunto: Re: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
Re: [firebird-support] Cache Performance Options
Karol, I have set my page buffers at 130,000 because I cannot go any higher. Still, the firebird process won't use more than 600MB of memory and I assume this is due to 32bit. Would upgrading to 64bit allow me to use more page buffers? Right now, I have my page buffers on that specific database set at 130,000 and my FileSystemCache Threshold set at 9,999,. This means it should always be using file system cache, if I'm reading the conf file correctly. From your recommendations, I should: Upgrade to 64bit version 2.5.3 and increase my page size to 16k If 64bit allows it, should I also increase my page buffers? Also, I assume async write-caching is okay. Is that correct? Finally, is it okay to disable auto sweeping and do it manually twice a day during slow times? Thanks for your advice, Josh On Mon, May 12, 2014 at 11:23 PM, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] firebird-support@yahoogroups.com wrote: Hi, Your database is not big but your settings are low. Change your FB server to 64bit and increase your dbbuffer or in fbconfig defaultdbcasche setting and use superserver. Check your database data page size. I always set it to 16KB. Your database utylize memory in this pattern: Dbpage size * dbbuffers Or if dbbuffer is 0 Db page size * defaultdbcachepages You must add to this sort buffer for any connection. You should also download shnapshot of firebird 2.5.3.xxx to fix windows 2008 memory problem Regards, Karol Bieniasxewski Wysłane z mojego HTC - Reply message - Od: Joshua Hartmann jhartm...@computekdental.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cache Performance Options Data: pon., maj 12, 2014 21:32 I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. The server is QEMU/KVM virtual server running Microsoft Server 2008 R2. The host machine is running Ubuntu Server 12.04. Xeon 1275 4 cores allocated to the virtual server 12GB memory allocated to the virtual server (32GB total on the host machine) 750GB Momentus Hybrid SSD/HDD's (RAID 0+1 1.5TB total usable) The database is ~30GB At first, they were running in classic server mode (FB version 2.1.3 x86) on a physical server 2003 machine with 4GB of memory. That machine's HDD's started to fail so we just moved it to the new virtual 2008 server. We let the software company do the install and they set it to classic mode (version 2.1.3 x86) but left all options at default. We noticed the server would start pretty fast but then throughout the day slow down. When checking the system's resources, I noticed it was maxing out on RAM and moving to the page file which explained the slowness. However, task manager did not display that memory was actually being used by the fbserver.exe processes. I asked the developer to switch it to superserver because I read its better for shared cache and since all the clients are accessing the same data (appointment lists, patient lists, etc) I thought it would speed things up. It didn't change the memory usage and fbserver.exe still only reported using about 60MB. So I started reading through articles online and found a bunch of optimization and cache settings. I changed the database options for async, set page buffers at 60k, and turned off auto sweeping. I also changed the firebird.conf settings. Specifically, the MaxFileSystemCache. None of it made any difference. Next, I upgraded to 2.5.2 (still x86) and its made a little difference. I still notice the fbserver using quite a bit of disk IO though and its still not very fast. I guess my questions are: In my situation, should I be using all file system cache or firebird cache? Essentially, what should I set my page buffers and FileSystemCacheThreshole to? I've already set my FileSystemCache to 90%. Should I enable or disable async? Should I enable auto sweeping or not? Would performance be faster if we upgraded to the 64bit version of 2.5.2? Thanks for any advice you can give. -Josh
Re: [firebird-support] Cache Performance Options
That is exactly what I'm doing right now. Its the only way to keep them going. I shut them down at lunch, run a sweep, then when they get back in, things are much faster. -Josh On Tue, May 13, 2014 at 8:35 AM, 'Fabiano - Desenvolvimento SCI' fabi...@sci10.com.br [firebird-support] firebird-support@yahoogroups.comwrote: Large gap is not caused by lack of auto sweep. It is bad system design. As you is not the system programmer, you can shut down all connections to the database during lunch time, then you can let users enter in your system again. After shutting down, run a manual sweep (gfix –sweep). If you see a increase in performance, your system is bad transaction designed. *De:* firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] *Enviada em:* terça-feira, 13 de maio de 2014 11:31 *Para:* firebird-support@yahoogroups.com *Assunto:* Re: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
RES: [firebird-support] Cache Performance Options
Josh, you have a system error. No Firebird configuration can deal with that scenario. The only solution is tell this to your system programmer. He will take a look and fix your system. The gap between transactions will be reduced and your system will run fast everything. Probably it will by a nightmare to your programmer… good look! De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: terça-feira, 13 de maio de 2014 11:42 Para: firebird-support@yahoogroups.com Assunto: Re: [firebird-support] Cache Performance Options That is exactly what I'm doing right now. Its the only way to keep them going. I shut them down at lunch, run a sweep, then when they get back in, things are much faster. -Josh On Tue, May 13, 2014 at 8:35 AM, 'Fabiano - Desenvolvimento SCI' fabi...@sci10.com.br [firebird-support] firebird-support@yahoogroups.com wrote: Large gap is not caused by lack of auto sweep. It is bad system design. As you is not the system programmer, you can shut down all connections to the database during lunch time, then you can let users enter in your system again. After shutting down, run a manual sweep (gfix –sweep). If you see a increase in performance, your system is bad transaction designed. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: terça-feira, 13 de maio de 2014 11:31 Para: firebird-support@yahoogroups.com Assunto: Re: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. -Josh On Tue, May 13, 2014 at 12:18 AM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: I am a MSP for a large dental office that uses an application with a Firebird DB. I've done a lot of database work in my life, but none with firebird so I need a little help with the configurations. The office is complaining of slowness and what we've done so far to help them will be below. Keep in mind, I am not the developer of the application, just a MSP trying to help them out since the developer has no clue what they're doing. We noticed the server would start pretty fast but then throughout the day slow down. Thanks for any advice you can give. A slow database can have lots of reasons, Josh. I take it that the 'large dental office' is amongst the largest companies that use the application in question, either in terms of database size or number of simultaneous users? Your observation that things slow down gradually, indicates that one possibility is that the developer hasn't thought thoroughly enough about transactions (a vital part of Firebird). Try running gstat when the database is slow, maybe you will see a large gap between oldest and next transactions. HTH, Set
RES: [firebird-support] Query optimization mystery
Your problem is: PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_)) Wish means a full table scan on SUPPROG. It is strange, because you have the index USV_SUPPROG_ADVOCATE_CODE ON field ADVOCATE_CODE Try this: select * from ( select a.User_ID from Advocate where a.USER_ID=37 ) as FILTER1, supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code Firebird will first filter the needed data and then join using the index USV_SUPPROG_ADVOCATE_CODE at table supprog. This, (at least for me) is always de faster way Firebird retrieve data. (Filter and/or order your data and then join with other tables) De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: terça-feira, 13 de maio de 2014 16:29 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] Query optimization mystery I've got a query optimization mystery I need some help with. The short version is I've got two tables that are very similar, but when I join each of them to a third table, I get different plans - one runs fast and the other runs slow. Here are the queries: select sp.STUDENTSEQ, a.User_ID from schlhist sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 The first runs with plan PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_SCHLHIST_ADVOCATE_CODE)) which is fast. The second runs with plan PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_)) which is slow. If I change the plan on the second to PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_supprog_ADVOCATE_CODE)) it also runs fast. I don't really understand statistics but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. That seems like a potentially important difference, but I'm not sure what it means or what to do about it. Here's the ddl (I've chopped out a lot fields I didn't think were relevant): CREATE TABLE ADVOCATE( ADVOCATE_CODE varchar(15), ADVOCATE varchar(20) COLLATE EN_US, TEACHINGCERT varchar(1) COLLATE EN_US, GENDATE timestamp, MODDATE timestamp, CHANGESTATUSFLAG smallint, REGIONCODE smallint, RETIREDCODE varchar(1), USER_ID integer ); CREATE UNIQUE INDEX ADVOCATE_ ON ADVOCATE (ADVOCATE_CODE); CREATE INDEX ADVOCATE_ADVOCATE ON ADVOCATE (ADVOCATE); CREATE INDEX ADVOCATE_REGIONCODE ON ADVOCATE (REGIONCODE); CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID); CREATE TABLE SCHLHIST( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, FACILITYID varchar(6) COLLATE EN_US, LQMDATE date, RESDATE date, FUNDINGDATE date, ENROLLDATE date, WITHDRAWDATE date, GENDATE timestamp, ADVOCATE_CODE varchar(15), COMMENT blob sub_type 1 ); CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ); CREATE INDEX SCHLHIST_FACILITYIDINDEX ON SCHLHIST (FACILITYID); CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE); CREATE INDEX SCHLHIST_STUDENTSEQINDEX ON SCHLHIST (STUDENTSEQ); CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID); CREATE INDEX USV_SCHLHIST_ADVOCATE_CODE ON SCHLHIST (ADVOCATE_CODE); CREATE TABLE SUPPROG( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, SPKEY varchar(15) COLLATE EN_US, SPCODE varchar(3) COLLATE EN_US, ADVOCATE_CODE varchar(15), OWNER_USER_ID integer ); CREATE UNIQUE INDEX SUPPROG_ ON SUPPROG (STUDENTSEQ,DOMID,DBID,SHSEQ,SPKEY); CREATE INDEX USV_SUPPROG_ADVOCATE_CODE ON SUPPROG (ADVOCATE_CODE); CREATE INDEX USV_SUPPROG_OWNER_USER_ID ON SUPPROG (OWNER_USER_ID);
RES: [firebird-support] Query optimization mystery
Maybe. Try update all index statistics. MAYBE it helps. In last case MAYBE a backup/restore can help too… (or drop and recreate those indexes) De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Enviada em: terça-feira, 13 de maio de 2014 17:15 Para: firebird-support@yahoogroups.com Assunto: Re: [firebird-support] Query optimization mystery Try this: select * from ( select a.User_ID from Advocate where a.USER_ID=37 ) as FILTER1, supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code This doesn't quite execute. I cleaned it up to this: select * from ( select a.User_ID, a.ADVOCATE_CODE from Advocate a where a.USER_ID=37 ) as FILTER1,supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code It gets the same plan: PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_)). But it has another problem, too: ultimately I'm wanting to use this as part of a view, so I can't do my filtering inside the query. Why isn't Firebird using the index? Do the index statistics have anything to do with it?
Re: [firebird-support] Query optimization mystery
Em 13/5/2014 17:14, Kevin Donn kd...@msedd.com [firebird-support] escreveu: Try this: select * from ( select a.User_ID from Advocate where a.USER_ID=37 ) as FILTER1, supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code This doesn't quite execute. I cleaned it up to this: select * from ( select a.User_ID, a.ADVOCATE_CODE from Advocate a where a.USER_ID=37 ) as FILTER1,supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code It gets the same plan: PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_)). But it has another problem, too: ultimately I'm wanting to use this as part of a view, so I can't do my filtering inside the query. Why isn't Firebird using the index? Do the index statistics have anything to do with it? Are the statistcs up to date ? The two tables you mentioned has the same amount of records ? The statistics are quite diferent, so, or the number of rows are bery diferent or the index statistics are out of date. Try this one: select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0 where a.USER_ID=37 see you !
RE: [firebird-support] Query optimization mystery
This doesn't quite execute. I cleaned it up to this: select * from ( select a.User_ID, a.ADVOCATE_CODE from Advocate a where a.USER_ID=37 ) as FILTER1,supprog sp Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code It gets the same plan: PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_)). But it has another problem, too: ultimately I'm wanting to use this as part of a view, so I can't do my filtering inside the query. Do not use SQL 87 JOIN syntax: ) as FILTER1,supprog sp It is very lazy and leave the relationships between the tables too undefined. Always use explicit JOIN. Like this: select sp.STUDENTSEQ, Filter1.User_ID from ( select a.User_ID, a.ADVOCATE_CODE from Advocate a where a.USER_ID=37 ) as FILTER1 JOIN supprog sp ON sp.ADVOCATE_CODE=FILTER1.Advocate_Code Questions: 1- What indexes do you have defined on a.User_ID? 2- What is the selectivity (aka uniqueness) of User_ID and Advocate_Code? 3- Is searching by User_ID and Advocate_Code a common query? Sean
RE: [firebird-support] Cache Performance Options
Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. Please provide the gstat header details for the database. Also you should also have a look at the SysInternals RAMMap tool, it provides insight in the application and system process which are consuming memory. This includes the OS file cache, which is the source of many problems with x64 version of Windows, and the reason why the amount of page cache activity is so high. Sean
Re: [firebird-support] Query optimization mystery
Are the statistcs up to date ? Statistics are not something I understand well. I have run SET STATISTICS INDEX USV_SUPPROG_OWNER_USER_ID on all indexes of the two tables. I'm guessing that brings them up to date, correct? No change in the query. The two tables you mentioned has the same amount of records ? The statistics are quite diferent, so, or the number of rows are bery diferent or the index statistics are out of date. SchlHist has 287565 records and 229320 where Advocate_Code is not null. SupProg has 929329 records and 2792 where Advocate_Code is not null. Try this one: select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0 where a.USER_ID=37 Because Advocate_Code is not an integer, I changed it to a.Advocate_Code || ''. And this does indeed cause it to use the index and run fast. I'd hate to have to use that as a final solution. Thanks for your input.
Re: [firebird-support] Query optimization mystery
On Tue, May 13, 2014 at 4:33 PM, 'Leyne, Sean' s...@broadviewsoftware.com[firebird-support] firebird-support@yahoogroups.com wrote: Questions: 1- What indexes do you have defined on a.User_ID? CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID); 2- What is the selectivity (aka uniqueness) of User_ID and Advocate_Code? I'm not sure I understand the question. I mentioned in a post I just did a few minutes ago that SupProg has 929329 records and 2792 where Advocate_Code is not null. Advocate has 2448 records and currently 0 records where User_ID is not null, although that will increase to may 200 as it goes into production. The ratio of non-null record will increase after this goes into production. 3- Is searching by User_ID and Advocate_Code a common query? The hope is that it will become so, but it the badly optimized query run in about 1.5s which is too high for production.
RE: [firebird-support] Cache Performance Options
Database header page information: Flags 0 Checksum12345 Generation 715138 Page size 4096 ODS version 11.2 Oldest transaction 676463 Oldest active 676464 Oldest snapshot 662552 Next transaction715100 Bumped transaction 1 Sequence number 0 Next attachment ID 3104 Implementation ID 16 Shadow count0 Page buffers13 Next header page0 Database dialect3 Creation date May 9, 2014 21:03:42 Attributes Variable header data: Sweep interval: 0 *END* This is after I did a manual sweep two hours ago. -Josh *Josh Hartmann* Senior Engineer COMPUtek Dental Systems P: 970-224-4022 F: 970-224-4001 *From:* firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] *Sent:* Tue., May. 13, 2014 3:39 PM *To:* firebird-support@yahoogroups.com *Subject:* RE: [firebird-support] Cache Performance Options Set, There is a large gap because I turned off auto sweeping. I disabled it because when it was sweeping, it was killing performance even more. Previous to the 2.5.2 update, it was also using all the memory and causing the server to swap every time it did a sweep (20,000 transactions). Right now, I have it sweeping once during their lunch downtime and again at the end of the day. It is faster than before the 2.5.2 update, but still not fast. Please provide the gstat header details for the database. Also you should also have a look at the SysInternals RAMMap tool, it provides insight in the application and system process which are consuming memory. This includes the OS file cache, which is the source of many problems with x64 version of Windows, and the reason why the amount of page cache activity is so high. Sean [Non-text portions of this message have been removed]
Re: [firebird-support] Query optimization mystery
Hi Kevin, let me first answer one of your particular questions: I don't really understand statistics but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. with the first index, you've about 3% of records with the same value in index (these are going to be searched sequentially), with the second, only 0.04%. Depending on the number of records (cardinality), this can make a really big difference. Next: AFAIK, Firebird changes the order of executing join-members (or left join members) by selectivity and cardinality. Because procedures result values can not be narrowed by index by the engine at execution time, they usually are evaluated at the end of the chain. If you know, that the stored procedure returns view records, connect it first and the rest by left join (left and straight join don't get shifted between each other by optimizer). If stored procedures result can be narrowed down by parameters on select time, try this: the result of SP never concernces about indicies. Thomas Am 13.05.2014 21:29, schrieb Kevin Donn kd...@msedd.com [firebird-support]: I've got a query optimization mystery I need some help with. The short version is I've got two tables that are very similar, but when I join each of them to a third table, I get different plans - one runs fast and the other runs slow. Here are the queries: select sp.STUDENTSEQ, a.User_ID from schlhist sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 The first runs with plan PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_SCHLHIST_ADVOCATE_CODE)) which is fast. The second runs with plan PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_)) which is slow. If I change the plan on the second to PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_supprog_ADVOCATE_CODE)) it also runs fast. I don't really understand statistics but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. That seems like a potentially important difference, but I'm not sure what it means or what to do about it. Here's the ddl (I've chopped out a lot fields I didn't think were relevant): CREATE TABLE ADVOCATE( ADVOCATE_CODE varchar(15), ADVOCATE varchar(20) COLLATE EN_US, TEACHINGCERT varchar(1) COLLATE EN_US, GENDATE timestamp, MODDATE timestamp, CHANGESTATUSFLAG smallint, REGIONCODE smallint, RETIREDCODE varchar(1), USER_ID integer ); CREATE UNIQUE INDEX ADVOCATE_ ON ADVOCATE (ADVOCATE_CODE); CREATE INDEX ADVOCATE_ADVOCATE ON ADVOCATE (ADVOCATE); CREATE INDEX ADVOCATE_REGIONCODE ON ADVOCATE (REGIONCODE); CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID); CREATE TABLE SCHLHIST( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, FACILITYID varchar(6) COLLATE EN_US, LQMDATE date, RESDATE date, FUNDINGDATE date, ENROLLDATE date, WITHDRAWDATE date, GENDATE timestamp, ADVOCATE_CODE varchar(15), COMMENT blob sub_type 1 ); CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ); CREATE INDEX SCHLHIST_FACILITYIDINDEX ON SCHLHIST (FACILITYID); CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE); CREATE INDEX SCHLHIST_STUDENTSEQINDEX ON SCHLHIST (STUDENTSEQ); CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID); CREATE INDEX USV_SCHLHIST_ADVOCATE_CODE ON SCHLHIST (ADVOCATE_CODE); CREATE TABLE SUPPROG( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, SPKEY varchar(15) COLLATE EN_US, SPCODE varchar(3) COLLATE EN_US, ADVOCATE_CODE varchar(15), OWNER_USER_ID integer ); CREATE UNIQUE INDEX SUPPROG_ ON SUPPROG (STUDENTSEQ,DOMID,DBID,SHSEQ,SPKEY); CREATE INDEX USV_SUPPROG_ADVOCATE_CODE ON SUPPROG (ADVOCATE_CODE); CREATE INDEX USV_SUPPROG_OWNER_USER_ID ON SUPPROG (OWNER_USER_ID); -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
RE: [firebird-support] Query optimization mystery
s...@broadviewsoftware.com [firebird-support] firebird- supp...@yahoogroups.com wrote: Questions: What about the SQL I recommended?? Sean
Re: [firebird-support] Query optimization mystery
Oops: If you know, that the stored procedure returns view records, connect ... I meant: few records -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
