RE: [firebird-support] Firebird get the list with all available id
If we are going the EXECUTE BLOCK approach, which I agree would be better. Then, the optimal logic would be: execute block returns (id integer) as declare variable Next_ID integer; begin ID = 1; FOR SELECT ID FROM Table ORDER BY ID INTO :Next_ID DO Next_ID = Next_ID - 1; WHILE (Next_ID = ID) DO BEGIN SUSPEND; ID = ID +1; END ID = ID +1; end Brilliant, Sean! Though I would suggest some minor refinement: EXECUTE BLOCK RETURNS (id integer) AS DECLARE VARIABLE Next_ID integer; BEGIN ID = 1; FOR SELECT ID FROM Table ORDER BY ID INTO :Next_ID DO BEGIN WHILE (Next_ID ID) DO BEGIN SUSPEND; ID = ID +1; END ID = ID +1; END END or even: EXECUTE BLOCK RETURNS (id integer) AS DECLARE VARIABLE Next_ID integer; BEGIN ID = 1; SELECT MIN(ID) FROM Table INTO :Next_ID; WHILE (Next_To ID) DO BEGIN SUSPEND; ID = ID +1; END FOR WITH TMP (ID) AS /*TMP.ID will contain first record of gap*/ (SELECT TF.ID+1 FROM Table TF LEFT JOIN Table TFN ON TF.ID + 1 = TFN.ID WHERE TFN.ID IS NULL) SELECT T.ID, MIN(TT.ID) /*TT.ID is first record after end of gap) FROM TMP T JOIN Table TT ON TF.ID TT.ID GROUP BY 1 INTO :ID, :Next_ID DO BEGIN WHILE (Next_ID ID) DO BEGIN SUSPEND; ID = ID +1; END END END Though this is more complicated logic and I am uncertain to which extent it benefits performance-wise. The rest is off topic Once again, your keen I found an error! I swear, English is my mother tongue. Although, from the above, you'd never know it! Once again, your keen *eye* found an error! Ah, at first I wondered whether it meant 'your keen self found an error', referring to my irritating skill to notice minor mistakes everywhere. I'd say the difference between a foreigner and a native English speaker is that some foreigners always do the same mistake, natives tend to do mistakes like this occasionally. I typically have a problem with the difference between RISE and RAISE, don't know whether I would have had less problems if English had been my native language. Also, I have observed one similar, but opposite, phenomena in Norwegian: AND and TO are pronounced identically, but spelt differently, and many Norwegians often write the wrong one. I think immigrants learning Norwegian are less prone to this mistake, though once they get fluent, some of them also start having this problem! Set
[firebird-support] Re: Firebird get the list with all available id
thank you all. I got also another solution, I will post it here maybe will help someone with recursive numbers as ( select 1 number from rdb$database union all select number+1 from rdb$database join numbers on numbers.number 1024 ) select n.number from numbers n where not exists (select 1 from table t where t.id = n.number) --- In firebird-support@yahoogroups.com, radubarbu84 wrote: In a table I have records with id's 2,4,5,8. How can I receive a list with values 1,3,6,7. I have tried in this way SELECT t1.id + 1 FROM table t1 WHERE NOT EXISTS ( SELECT * FROM table t2 WHERE t2.id = t1.id + 1 ) but it's not working correctly. It doesn't bring all available positions. Is it possible without creating another table?
[firebird-support] Re: isc_attach_database, EAccessViolation in modul 'fbclient.dll'
--- In firebird-support@yahoogroups.com, patrick_marten wrote: Hello, on a customer machine in our internal log I've noticed the following error: isc_attach_database EAccessViolation Accessviolation at address 1004D5EB in modul 'fbclient.dll'. Writing of address 0044. It's a Windows 8 PC running as a clinet, which connects to a Windows 7 PC (server) where FB 2.5.2 is installed. Everything seems to work, the data is visible on the client side, can be edited etc. But I'm somewhat worried about that message... Have never seen it before at our customers machines. Any ideas about the reasons for the problem here? Best regards, Patrick Hello, now a colleague of mine is able to reproduce the issue on his machine as well. Our application has an exception logger using JVCL. At some point the application calls JclStopExceptionTracking and that's when the AV in fbclient.dll occurs for some weird reason... Even more weird is the fact, that this works fine several times before the AV and afterwards, so in that one case something seems to special. Not sure if that's a bug in Firebird or in JVCL or in both. This seems to happen since we have upgraded Firebird from 2.1.3 to 2.5.2, so I think it has at least partly to do with Firebird as well... JVCL version remained the same so far. Any ideas / suggestions are welcome! Kind regards, Patrick
[firebird-support] Optimal variant
Hi all. I wonder which of these two variants is optimal, the objective is to select data depending on the input parameters: 1 variant - The selection procedure involves all joins: SET TERM ^; CREATE PROCEDURE my_select_proc ( param1 TYPE OF COLUMN table1.field1, param2 TYPE OF COLUMN table2.field1) RETURNS ( field1_T1 TYPE OF COLUMN table1.field1, field2_T1 TYPE OF COLUMN table1.field2, field1_T2 TYPE OF COLUMN table2.field1, field2_T2 TYPE OF COLUMN table2.field2) AS BEGIN FOR SELECT T1.Field1, T1.Field2, T2.Field1, T2.Field2 FROM table1 T1 INNER JOIN table2 T2 ON T2.id = T1.id WHERE T1.Field1 = param1 AND T2.Field2 = param2 INTO :field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN SUSPEND; END END^ SET TERM ;^ 2 variant- Create a view, and use them in the procedure: SET TERM ^; CREATE VIEW my_view (field1_T1, field2_T1, field1_T2, field2_T2) AS SELECT T1.Field1 field1_T1, T1.Field2 field2_T1, T2.Field1 field1_T2, T2.Field2 field2_T2 FROM table1 T1 INNER JOIN table2 T2 ON T2.id = T1.id; CREATE PROCEDURE my_select_proc ( param1 TYPE OF COLUMN table1.field1, param2 TYPE OF COLUMN table2.field1) RETURNS ( field1_T1 TYPE OF COLUMN table1.field1, field2_T1 TYPE OF COLUMN table1.field2, field1_T2 TYPE OF COLUMN table2.field1, field2_T2 TYPE OF COLUMN table2.field2) AS BEGIN FOR SELECT V.field1_T1, V.field2_T1, V.field1_T2, V.field2_T2 FROM my_view V WHERE V.field1_T1 = param1 AND V.field1_T2 = param2 INTO :field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN SUSPEND; END END^ SET TERM ;^ Best Regards, Sergio PD: The selection can include up to ten tables. I use FB SS v2.5.1
[firebird-support] SUSPEND
I have a CPU intensive stored procedure. It only runs occasionally at user request, and when it runs, my server is busy running the stored proc, and other users using the same DB notice a severe delay or they even get a not responding message on my Delphi front end. I have some loops going on in the stored proc where I supposed I could put some SUSPEND statements but I just want to be sure about how SUSPEND works. There are two possible scenarios and I need to know which is true: a) SUSPEND is like a Application.ProcessMessages in Windows API/Delphi. Which means, I can throw in as many as I like in my stored proc and at any place. It will allow a chance for communication between calling client and server. (a.1: and will also not overload the server so much, allowing other clients to continue, perhaps with some delay, but not as bad as without the SUSPEND statements) b) SUSPEND is like EXIT, in that process flow jumps out of the stored proc at the point where it appears, and any lines following the SUSPEND do not get executed Which statement is true, and is sub-statement a.1 true? Thanks in advance for any help you can provide.
[firebird-support] GBAK Scheduler won't install as service on Windows 2008 Server
I'm not sure if this is the correct forum for this, so please let me know if it should go somewhere else. I am configuring a new Windows 2008 server (64-bit) to host our Firebird installation (SuperServer v2.5.2). I have installed FB and it is running fine. When I installed the latest version of GBAK Scheduler (1.0.10), however, I ran into problems. The main application runs as expected, but I cannot get it to install as a service. I am an admin on the server, so that shouldn't be the issue. When I open the GUI, it gives me an error message saying that GBAK Scheduler service is not installed. I have run the GBAKSRV.exe several times and it appears to run, but nothing happens. I appreciate any help on this. Scott Moon
Re: [firebird-support] SUSPEND
On 30-1-2013 19:41, red_october2009 wrote: I have a CPU intensive stored procedure. It only runs occasionally at user request, and when it runs, my server is busy running the stored proc, and other users using the same DB notice a severe delay or they even get a not responding message on my Delphi front end. I have some loops going on in the stored proc where I supposed I could put some SUSPEND statements but I just want to be sure about how SUSPEND works. There are two possible scenarios and I need to know which is true: a) SUSPEND is like a Application.ProcessMessages in Windows API/Delphi. Which means, I can throw in as many as I like in my stored proc and at any place. It will allow a chance for communication between calling client and server. (a.1: and will also not overload the server so much, allowing other clients to continue, perhaps with some delay, but not as bad as without the SUSPEND statements) b) SUSPEND is like EXIT, in that process flow jumps out of the stored proc at the point where it appears, and any lines following the SUSPEND do not get executed Which statement is true, and is sub-statement a.1 true? Thanks in advance for any help you can provide. It is actually none of the above (it is almost a, but not entirely). SUSPEND signals that the current values of the output variables should be returned to the client. In essence any stored procedure that contains SUSPEND is a selectable stored procedure. Each call to SUSPEND produce a new row (with the current values of the output variables). Or as the Interbase 6.0 Language Reference (page 177) says: Suspends execution of a select procedure until the next FETCH is issued and returns values to the calling application. Available in stored procedures only. Syntax SUSPEND; Description The SUSPEND statement: * Suspends execution of a stored procedure until the application issues the next FETCH. * Returns values of output parameters, if any. A procedure should ensure that all output parameters are assigned values before a SUSPEND. SUSPEND should not be used in an executable procedure. Use EXIT instead to indicate to the reader explicitly that the statement terminates the procedure. Mark -- Mark Rotteveel
Re: [firebird-support] GBAK Scheduler won't install as service on Windows 2008 Server
Hello, ScottMoon! Wednesday, January 30, 2013, 10:45:28 PM, you wrote: S I'm not sure if this is the correct forum for this, so please let S me know if it should go somewhere else. S I am configuring a new Windows 2008 server (64-bit) to host our S Firebird installation (SuperServer v2.5.2). I have installed FB and it is running fine. S When I installed the latest version of GBAK Scheduler (1.0.10), S however, I ran into problems. The main application runs as expected, but I cannot get it If you mean this https://sites.google.com/site/gbakscheduler/ it is old, and yes, it can have access rights problem. I also, with one of my applications running as service, have an unknown and unresolved access rights problem even with starting service from an application on Windows 2003 Server and another server editions (do not have this problem on any desktop OS). The solution is only - run installation from Administrator - start service from Administrator If we were speaking about YOUR application, I would sent you to MSDN or MS knowledgebase, but Gbak Scheduler is a third party product, and you need to ask it's author about any problems with it. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] monitoring firebirdsql with opensource tool ?
* other interesting requests ? to prevent or diagnose quickly problems ? I sometimes use (well, I don't actually include any ORDER BY, but would if there were lots of simultaneous activity going on) select whatever from mon$statements where mon$transaction_id current_transaction order by mon$timestamp to find statements that are currently running (typically statements that I've issued without taking a proper look on the plan or where I notice an error while it is executing so that I want to terminate that particular request). I think it could be used to find candidates for being long-running queries. Though I use Fb 2.5 and don't know whether it will work or not on 2.1. This particular statement will work in 2.1. ;-) Thomas
[firebird-support] client tools only install on synology/arm ?
Hello to do my monitoring, I want to use a different box than the fb server, a NAS (synology) but I need only client tools (isql, gbak, ...) On synology, it's possible to bootstrap/ipkg [1] to get more tools than normally available but sadly firebirdsql is not in repository (see probably why at the end). I have ipkg installed w autoconf, automake, bison, gcc, make, ncurses-dev and try to compile Firebird (2.5.1) autogen.sh runs in issue w different perl threading compiling options so I run directly configure # ./configure --prefix=/opt/firebird [...] The Firebird2 package has been configured with the following options: Architecture : ClassicServer Debug : disabled 64 bit I/O for 32-bit platforms : enabled Raw devices : enabled Service name : gds_db Service port : 3050 GPRE modules : c_cxx.cpp Install Dir : /opt/firebird But # make make -C gen firebird make[1]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/gen' make -C ../extern/btyacc make[2]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/extern/btyacc' cc-c -o closure.o closure.c make[2]: cc: Command not found I would have supposed configure to match cc and gcc. went the easy way ln -s /opt/bin/gcc /opt/bin/cc But (2) make -C gen firebird make[1]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/gen' (cd ..; ./src/misc/writeBuildNum.sh rebuildHeader) files are identical make boot_phase1_build make[2]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/gen' make -j1 -f ../gen/Makefile.boot.gpre gpre_boot make[3]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/gen' g++ -DBOOT_BUILD -I../src/include/gen -I../src/include -I../src/vulcan -DNAMESPACE=Vulcan -O3 -DNDEBUG -DLINUX -DARM -pipe -p -MMD -fPIC -fsigned-char -fmessage-length=0-c ../src/jrd/gds.cpp -o ../temp/boot/jrd/gds.o In file included from ../src/include/../common/classes/alloc.h:43, from ../src/include/../common/classes/fb_string.h:39, from ../src/jrd/../jrd/os/path_utils.h:31, from ../src/jrd/gds.cpp:47: ../src/include/../common/classes/fb_atomic.h:521:2: error: #error AtomicCounter: implement appropriate code for your platform ! = it seems armv5tel is not supported ... no just because I missed lib atomic or atomic_ops [3] (in this case, configure should have just failed). After installing, compilation went fine. $ LDFLAGS=-L/opt/firebird/lib CFLAGS=-I/opt/firebird/include LIBS=-L/opt/firebird/lib CPPFLAGS=-I/opt/firebird/include ./configure --prefix=/opt/firebird [...] $ LDFLAGS=-L/opt/firebird/lib CFLAGS=-I/opt/firebird/include LIBS=-L/opt/firebird/lib CPPFLAGS=-I/opt/firebird/include make [...] make -j1 -f ../gen/Makefile.boot.gpre gpre_boot make[3]: Entering directory `/opt/tmp/Firebird-2.5.2.26539-0/gen' g++ -L/opt/firebird/lib -Wl,-rpath,/opt/firebird/lib -Wl,-rpath,/opt/firebird/intl ../temp/boot/jrd/dsc.o ../temp/boot/jr ../temp/boot/jrd/dsc.o: In function `DSC_convert_to_text_length(unsigned short)': dsc.cpp:(.text+0x8): undefined reference to `__gnu_mcount_nc' ../temp/boot/jrd/dsc.o: In function `DSC_make_descriptor(dsc*, unsigned short, short, unsigned short, short, short, short)': dsc.cpp:(.text+0x54): undefined reference to `__gnu_mcount_nc' ../temp/boot/jrd/dsc.o: In function `DSC_string_length(dsc const*)': dsc.cpp:(.text+0x250): undefined reference to `__gnu_mcount_nc' ../temp/boot/jrd/dsc.o: In function `dsc::getStringLength() const': dsc.cpp:(.text+0x30c): undefined reference to `__gnu_mcount_nc' ../temp/boot/jrd/dsc.o: In function `DSC_dtype_tostring(unsigned char)': dsc.cpp:(.text+0x320): undefined reference to `__gnu_mcount_nc' ../temp/boot/jrd/dsc.o:dsc.cpp:(.text+0x368): more undefined references to `__gnu_mcount_nc' follow ../temp/boot/common/fb_exception.o: In function `(anonymous namespace)::StringsBuffer::ThreadBuffer::thisThread(int)': fb_exception.cpp:(.text+0x179c): undefined reference to `pthread_kill' ../temp/boot/common/fb_exception.o: In function `Firebird::status_exception::status_exception(int const*)': fb_exception.cpp:(.text+0x17bc): undefined reference to `__gnu_mcount_nc' ../temp/boot/common/fb_exception.o: In function `Firebird::status_exception::raise(Firebird::Arg::StatusVector const)': fb_exception.cpp:(.text+0x1818): undefined reference to `__gnu_mcount_nc' ../temp/boot/common/fb_exception.o: In function `Firebird::status_exception::raise(int const*)': fb_exception.cpp:(.text+0x187c): undefined reference to `__gnu_mcount_nc' ../temp/boot/common/fb_exception.o: In function `Firebird::status_exception::status_exception(int const*)': fb_exception.cpp:(.text+0x18cc): undefined reference to `__gnu_mcount_nc' ../temp/boot/common/fb_exception.o: In function `Firebird::makePermanentVector(int*, int)': fb_exception.cpp:(.text+0x1928): undefined reference to `__gnu_mcount_nc'
[firebird-support] Re: SUSPEND
Hi Mark, This is an EXECUTEable proc, returning nothing, so I should not use SUSPEND. Do (or anyone) have a suggestion as to how I can tell the stored proc to not bring my server to it's knees for 3 full minutes? I want FB to be given high(est) level thread priority in the OS, except for this one procedure, I'd like FB to rather mozy along, and serve other user's requests first and get back to this thread when it can. Thanks.
Re: [firebird-support] GBAK Scheduler won't install as service on Windows 2008 Server
I'm not sure if this is the correct forum for this, so please let me know if it should go somewhere else. I am configuring a new Windows 2008 server (64-bit) to host our Firebird installation (SuperServer v2.5.2). I have installed FB and it is running fine. When I installed the latest version of GBAK Scheduler (1.0.10), however, I ran into problems. The main application runs as expected, but I cannot get it to install as a service. I am an admin on the server, so that shouldn't be the issue. When I open the GUI, it gives me an error message saying that GBAK Scheduler service is not installed. I have run the GBAKSRV.exe several times and it appears to run, but nothing happens. I appreciate any help on this. As Dmitry has mentioned, it's old and there are alternatives out there, although I haven't used them myself. Simple google for firebird database backup scheduler. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Re: SUSPEND
Hi, This is an EXECUTEable proc, returning nothing, so I should not use SUSPEND. Do (or anyone) have a suggestion as to how I can tell the stored proc to not bring my server to it's knees for 3 full minutes? I want FB to be given high(est) level thread priority in the OS, except for this one procedure, I'd like FB to rather mozy along, and serve other user's requests first and get back to this thread when it can. What is your procedure doing? How does the execution plan look like etc ...? Unfortunately, Firebird doesn't offer configurable resource pools to configure e.g. max allotted CPU usage etc. like in Oracle, SQL Server etc. -- With regards, Thomas Steinmaurer http://www.upscene.com/
[firebird-support] Re: SUSPEND
Unfortunately, Firebird doesn't offer configurable resource pools Something like Delphi's sleep(1); could be an easy solution without having to introduce resource pools
[firebird-support] Re: SUSPEND
Yes Ed, Delphi's Sleep, or even something like Application.ProcessMessages in MS Windows. Just to say to the OS, hey, you can ignore me for a few milliseconds if there are other requests coming in, if not, keep going. Suggested command name: PAUSE or PAUSEME
Re: [firebird-support] Re: SUSPEND
You did not write, which server version you are using and on which hardware. FB 2.5.2.26539 I have one intensively used ClassicServer 1.5.6 (...yeah... *am* in the process of migrating to 2.5.2) running on a dedicated machine with Windows2003 OS. 4 GB Ram only, which seems ridiculously little today. Never feel bad about using old hardware or old FB. FB is very efficient and doesn't need a whole lot. If the user group is happy, FB version is not relevant. Each time the stored proc is run it has to delete about 30K records and add just over a million records. * Garbage collection might kick in here * Updating active indexes takes time as well, especially with a small page cache. Perhaps you can deactivate indexes before running the SP and activate them afterwards? * Depending on how you get your records for inserting, this might be inefficient due to a bad PLAN (missing index, bad index, out-dated statistics) * Are you using EXECUTE STATEMENT in your SP -- With regards, Thomas Steinmaurer http://www.upscene.com/
[firebird-support] Re: SUSPEND
Hi Thomas, * Garbage collection might kick in here I have sweep set to manual and I do it every night just before the backup using gbak. I don't know if that answers your question about garbage collection. * Updating active indexes takes time as well, especially with a small page cache. I've never played around with the page cache. There is so much caching going on in my system, I was afraid to add another, or make any changes. What size do you recommend and where would I set it? I imagine it would require a restart of the FB service (which is ok) Perhaps you can deactivate indexes before running the SP and activate them afterwards? There are a ton of indexes that are being updated on each insert. Unfortunately, I cannot switch off the indexes because they are all constantly in use by the stored proc itself, as well as other users on the system. Depending on how you get your records for inserting, this might be inefficient due to a bad PLAN (missing index, bad index, out-dated statistics) The PLAN seems ok. Nothing is coming out as NATURAL. All correct indexes are being used. * Are you using EXECUTE STATEMENT in your SP Yes, about 4 of them. I have to ensure that all 4 are either successful or rolled back, so they must all be enclosed in one (mother of all) transaction(s). Thanks for your help Thomas. Further on my suggestion about PAUSEME: It would be cool to have a built in variable named CYCLE. So, a stored proc could be written like: FOR SELECT GIGIDY FROM GOBELY INTO :PAR1 DO BEGIN IF CYCLE MOD 1000 = 0 THEN PAUSEME; END The PAUSEME command should allow the OS and FB to turn it's attention to other threads requesting attention. (I know... it gets complicated) PAUSEME duration should be configurable in the fb config file. Range 5 millisec to 1000 millisec.
[firebird-support] Side by side install 2.1 and 2.5?
Hi, On a Windows server with FB 2.1 I want to try using FB 2.5 for some purposes, so I want to do a side by side install of 2.5. The installer asks me to stop the existing 2.1 instances, so I suppose it cannot be done using the installer? So how should I do it? get 2.5 zip package and just unpack it in a separate folder? I don't really like to do that under Program files... It's the *installation* I'm asking about, not *configuration*, i.e. I know about setting a different port number, e.g. 3051 in firebird.conf. Regards, Kjell -- -- Kjell Rilbe DataDIA AB E-post: kjell.ri...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64
Re: [firebird-support] Side by side install 2.1 and 2.5?
On a Windows server with FB 2.1 I want to try using FB 2.5 for some purposes, so I want to do a side by side install of 2.5. The installer asks me to stop the existing 2.1 instances, so I suppose it cannot be done using the installer? Download the ZIP archive. Then read the file install_windows_manually.txt inside. So how should I do it? get 2.5 zip package and just unpack it in a separate folder? I don't really like to do that under Program files... Basically, yes. You will need to call instsvc.exe, too. It doesn't have to be Program Files. Josef ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Re: SUSPEND
(André wrote): I have one intensively used ClassicServer André's suggestion to use classic server on a computer with multiple processors/cores sounds like the simplest way to circumvent your problem (provided things work better nowadays, in ancient days we had to set CPUAffinity to only use one processor due to problems with Windows constantly switching between processors, myself I've never used classic server). At least I think that you either use SuperServer, very old hardware or have set CPUAffinity to use only one processor on your server. (the rest are some comments regarding Red Octobers various emails) Each time the stored proc is run it has to delete about 30K records and add just over a million records. Sometimes, a solution can be to make small updates frequently rather than large updates rarely. Don't know whether this is possible in your situation or whether all data naturally come in large batches. There are a ton of indexes that are being updated on each insert. Before creating an index, one should consider if it would have adverse effects, it can be the case that an index is good for one situation, but ruins another. Ton of indexes sounds to me as you have too many, but a TON in database terms is of course quite a subjective measure ;o) Particularly, if you have lots of composite indexes, I would consider whether it could be beneficial to replace them by having individual indexes on the fields (well, possibly one or two compound indexes if they are critical). Combining 2 fields is not bad if a field is only the first field in one index (could be slightly quicker, but would be slightly more difficult to read the plan). However, if you have 3 fields and use them in compound indexes, then you could potentially have 6 indexes to cover all combinations, if you have 4 fields, I assume the potential number of useful indexes to increase to 24, with 5 fields 120 indexes and so on. Although no-one would create all such combinations, you can easily see how the indexes can multiply if you use composite indexes. Hence, I prefer to only have single field indexes for fields that require indexing and hardly ever use composite indexes. One of the benefits of Firebird, is that it can use several indexes for one table within a query, many other databases cannot and require composite indexes. The PLAN seems ok. Nothing is coming out as NATURAL. All correct indexes are being used. NATURAL is sometimes the best option, and brilliant indexes can be lousy for certain queries. E.g. SELECT * FROM TableA A JOIN TableB B on A.ID B.ID WHERE A.ID = (select min(B2.ID) FROM TableB B2) would probably use an index for B.ID, but with such a query, that would be considerably worse than NATURAL (the index would be used to eliminate ONE record). Further on my suggestion about PAUSEME: It would be cool to have a built in variable named CYCLE. So, a stored proc could be written like: FOR SELECT GIGIDY FROM GOBELY INTO :PAR1 DO BEGIN IF CYCLE MOD 1000 = 0 THEN PAUSEME; END Maybe you could have an input parameter in your stored procedure signaling a starting point and then only process 1000 records before calling EXIT? That way, the pausing would be in your Delphi application and not Firebird. Do (or anyone) have a suggestion as to how I can tell the stored proc to not bring my server to it's knees for 3 full minutes? 3 minutes to insert 1 million records sounds quite normal. HTH, Set
