slow performance on foreign key fetch (with lots of "null" values)?

[Former Member]

Hi all,

I've got this (rather large) table (4.2 M rows) that has a foreign key column ("fk_artpres_schemes_id") that contains all "null" values at the moment.

Now from my client application I call a stored procedure that should gather some data for a specific foreign key value ("pi_id" in the example below). It should return nothing specific as there are no values present in the db at all for all non null values. In my opinion this query should finish really fast as no rows match the foreign key value ...

Now the strange part: my application calls this stored proc 30 times (say with id's 1 to 30) and now for most values the sp returns subsecond (as expected), but for some values it takes 20 seconds (!) to complete this query. Most of the time 2 or 3 out of these 30 take way too much time ... (roughly between 5 to 10 run fast and then a (random) value takes for ever)

When I rerun the same 30 queries again it's a another 2 or 3 id's that take a lot of time. While nothing has changed to the db.

I've added an index to this foreign key column, it now reduces the slow queries to roughly 10 seconds, but it's still way too slow (IMHO) for 2 to 3 out of 30 queries.

below is the offending query (that's in a stored procedure). I've currently changed it to select only "1" so it can only be the filtering of the rows that should take time:

If anybody has a clue how to increase the performance on this, be my guest, I'm clueless

TIA

  message 'pre Q 1';
  select
    1, //count(distinct vkreg.fk_par_id),
    1, //count(vkreg.id),
    1, //sum(if vkreg.aantal > 0 then 1 else 0 end if),
    1, //sum(if isnull(vkreg.pres_besteld,0) = 0 and vkreg.aantal > 0 then 1 else 0 end if),
    1, //count(distinct if vkreg.aantal > 0 then fk_artnum else null end if),
    1 //sum(aantal)
  into 
    lCnt_modules,
    lCnt_details,
    lCnt_details_quantified,
    lCnt_details_orderable,
    lCnt_products,
    lSum_quantity
  from vkreg 
  where fk_artpres_schemes_id = pi_id;
  //
  message 'post Q 1';

the database server is version 11.01.2436

[justin_willey]

Can you post the post the structure of the vkreg table and its indexes / fks? (You can just cut and past from the table / index lists in Sybase Central to get the CREATE TABLE etc statements)

[Former Member]

it's rather large, I had to split it up:

// table part 1 CREATE TABLE "dbo"."vkreg" ( "siteid" INTEGER NOT NULL, "id" INTEGER NOT NULL, "fk_vkbesiteid" SMALLINT NULL, "fk_vkbeid" INTEGER NULL, "fk_vkbositeid" SMALLINT NULL, "fk_vkboid" INTEGER NULL, "fk_vkdocsiteid" INTEGER NULL, "fk_vkdocid" INTEGER NULL, "artcode" VARCHAR(15) NULL, "objequ" TINYINT NULL DEFAULT 0, "catcode" VARCHAR(4) NULL, "grpcode" VARCHAR(4) NULL, "levcode" VARCHAR(10) NULL, "regstat" SMALLINT NULL, "aantal" DECIMAL(9,2) NULL, "vkeenh" TINYINT NULL, "btwcode" TINYINT NULL, "herkomst" TINYINT NULL, "bapr" DECIMAL(15,6) NULL, "vkprijs" DECIMAL(12,2) NULL, "reknr" INTEGER NULL, "exexp" TINYINT NULL, "fk_artsiteid" INTEGER NULL, "fk_artnum" INTEGER NULL, "fk_gwsiteid" INTEGER NULL, "fk_gwid" INTEGER NULL, "gwnaam" VARCHAR(80) NULL, "gwlet" VARCHAR(8) NULL, "grn_tltwijz" VARCHAR(1) NULL, "grn_potmaat" VARCHAR(20) NULL, "grn_stmhgt" VARCHAR(25) NULL, "grn_maat" VARCHAR(25) NULL, "grn_om" VARCHAR(25) NULL, "grn_foto" TINYINT NULL, "fk_resregsiteid" INTEGER NULL, "fk_resregid" INTEGER NULL, "fk_fustsiteid" INTEGER NULL, "fk_fustid" INTEGER NULL, "ncolli" DECIMAL(13,6) NULL, "npercolli" INTEGER NULL, "fk_kleurid" SMALLINT NULL, "fk_gwblwid" SMALLINT NULL, "vkcom" DECIMAL(5,2) NULL, "vknocom" TINYINT NULL, "fk_locid" SMALLINT NULL, "locsub" SMALLINT NULL, "restantnaj" TINYINT NULL, "fk_clustercode" VARCHAR(3) NULL, "clusaant" VARCHAR(10) NULL, "adat" DATE NULL, "atijd" TIME NULL, "ausnr" SMALLINT NULL, "mdat" DATE NULL, "mtijd" TIME NULL, "musnr" SMALLINT NULL, "fk_vkofregsiteid" SMALLINT NULL, "fk_vkofregid" INTEGER NULL, "regkort" DECIMAL(6,2) NULL, "afn_ref" VARCHAR(200) NULL, "grn_direct" TINYINT NULL, "fk_mesid" INTEGER NULL, "foreignsiteid" INTEGER NULL, "foreignid" INTEGER NULL, "status1" INTEGER NULL, "datbo" INTEGER NULL, "qkorting" DECIMAL(6,2) NULL, "qkortfix" TINYINT NULL, "vkprijsfix" TINYINT NULL, "pkprijs" DECIMAL(15,6) NULL, "afgeboekt" TINYINT NULL DEFAULT 0, "vkprijs_h" DECIMAL(15,6) NULL, "advprijs" DECIMAL(15,6) NULL, "tkkenm" VARCHAR(20) NULL, "fk_ikregsiteid" SMALLINT NULL, "fk_ikregid" INTEGER NULL, "edibonus" TINYINT NULL, "blokartn" TINYINT NULL, "n_scan" SMALLINT NULL, "nII" DECIMAL(13,6) NULL, "nIII" DECIMAL(13,6) NULL, "fuqty_id" INTEGER NULL, "cntncolli" DECIMAL(13,6) NULL, "c_aantal" DECIMAL(9,2) NULL, "c_fk_fustsiteid" INTEGER NULL, "c_fk_fustid" INTEGER NULL, "c_ncolli" DECIMAL(13,6) NULL, "c_cntncolli" DECIMAL(13,6) NULL, "c_npercolli" INTEGER NULL, "c_nII" DECIMAL(13,6) NULL, "c_nIII" DECIMAL(13,6) NULL, "c_fuqty_id" INTEGER NULL, "fk_vkmix_id" INTEGER NULL, "fk_par_id" INTEGER NULL, "isparent" TINYINT NULL, "nf" INTEGER NULL, "tf" TINYINT NULL, "pr1" TINYINT NULL, "pr2" TINYINT NULL, "vrbn" TINYINT NULL, "ikprijs" DECIMAL(8,2) NULL, "ikreknr" INTEGER NULL, "extrapr" VARCHAR(20) NULL, "nakbnr" INTEGER NULL, "pr3" TINYINT NULL, "vkpr0" DECIMAL(8,2) NULL, "vkpr1" DECIMAL(8,2) NULL, "vkpr2" DECIMAL(8,2) NULL, "vkpr3" DECIMAL(8,2) NULL, "mixtype" TINYINT NULL, "c_mixtype" TINYINT NULL, "nicelb1" TINYINT NULL, "nicelb2" TINYINT NULL, "nicelb3" TINYINT NULL, "nicelb4" TINYINT NULL, "uitpr_bev" TINYINT NULL, "lev_uitprijzen" TINYINT NULL, "pr_compl34" TINYINT NULL, "pr_cnt12" INTEGER NULL, "fk_sourcevkbe_siteid" INTEGER NULL, "fk_sourcevkbe_id" INTEGER NULL, "edistat" TINYINT NULL, "fk_privlab_id" INTEGER NULL, "fk_artmix_id" INTEGER NULL, "tarief_lev_uitpr" DECIMAL(8,2) NULL, "allin" TINYINT NULL, "logopm" VARCHAR(140) NULL, "fk_memo_sys_id" INTEGER NULL, "fk_rellocatie_relnum" INTEGER NULL, "fk_art_concept_id" INTEGER NULL, "ik_srt" INTEGER NULL, "srt_log" VARCHAR(92) NULL, "srt_art" VARCHAR(110) NULL, "ik_cc_kortperc" DECIMAL(6,2) NULL, "vk_cc_kortperc" DECIMAL(6,2) NULL, "is_kader" TINYINT NULL, "is_kaderdetail" TINYINT NULL, "fk_art_potm_id" INTEGER NULL, "advprijs_valcode" VARCHAR(3) NULL, "vrf" TINYINT NULL, "fk_extomid_off" SMALLINT NULL, "fk_fudrager_id" INTEGER NULL,

[Former Member]

// part two

"fk_memo_sys_id_edi" INTEGER NULL,
"fl_status_or" TINYINT NULL,
"fl_status_rsp" TINYINT NULL,
"edistat_fl" TINYINT NULL,
"fl_by_on" VARCHAR(80) NULL,
"no_cleanup" TINYINT NULL,
"geen_uitprijzen" TINYINT NULL,
"futodocfu" TINYINT NULL,
"fl_status_rsp_freeze" TINYINT NULL,
"pdkeuring_status" TINYINT NULL,
"fk_artpres_schemes_id" INTEGER NULL,
"week_vanaf" SMALLINT NULL,
"week_tm" SMALLINT NULL,
"pres_loc" VARCHAR(10) NULL,
"pres_besteld" TINYINT NULL,
"fk_module_id" INTEGER NULL,
"fuqty_qty" INTEGER NULL,
"fk_vkofpresetdet_id" INTEGER NULL,
"cd" TINYINT NULL,
"zz_f_trig" TINYINT NULL,
"zz_comp_iktrp" DECIMAL(12,4) NULL,
"zz_comp_ikopsl" DECIMAL(12,4) NULL,
"zz_comp_vkopsl" DECIMAL(12,4) NULL,
"zz_comp_vkikopsl" DECIMAL(12,4) NULL,
"zz_comp_verd" DECIMAL(12,4) NULL,
"zz_comp_verd_applied" TINYINT NULL,
"zz_comp_fust" DECIMAL(12,4) NULL,
"zz_comp_uitpr" DECIMAL(12,4) NULL,
"zz_comp_vktrp" DECIMAL(12,4) NULL,
"zz_comp_vktrp_applied" TINYINT NULL,
"zz_comp_afdracht" DECIMAL(12,4) NULL,
"zz_comp_iktrpperc" DECIMAL(12,4) NULL,
"zz_comp_ikkort" DECIMAL(12,4) NULL,
"zz_comp_verdperc" DECIMAL(12,4) NULL,
"zz_comp_vktrpperc" DECIMAL(12,4) NULL,
"zz_calctype" TINYINT NULL,
"zz_ikpr" DECIMAL(12,3) NULL,
"zz_vkpr" DECIMAL(12,3) NULL,
"zz_zz_custloaded" TINYINT NULL,
"zz_ik_cc_korting" DECIMAL(12,4) NULL,
"zz_vk_cc_korting" DECIMAL(12,4) NULL,
"zz_custloaded" TINYINT NULL,
"zz_comp_vkopsl1_r" DECIMAL(12,4) NULL,
"zz_comp_vkopsl2_r" DECIMAL(12,4) NULL,
"zz_comp_verd_r" DECIMAL(12,4) NULL,
"zz_comp_verdperc_r" DECIMAL(12,4) NULL,
"zz_comp_uitpr_r" DECIMAL(12,4) NULL,
"zz_comp_afdracht_r" DECIMAL(12,4) NULL,
"totaal" DECIMAL(12,2) NULL,
"zz_datle" DATE NULL,
"fk_dynalabels_id" INTEGER NULL,
"tarief_lev_uitpr_ik" DECIMAL(8,2) NULL,
"ext_nicelb" VARCHAR(15) NULL,
"app_ausnr" INTEGER NULL,
"pricing_instr" INTEGER NULL,
"app_musnr" INTEGER NULL,
PRIMARY KEY ( "siteid" ASC, "id" ASC )

) IN "system";

[Former Member]

// foreign keys and indexes part 1:

ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "fust_fk_fust" FOREIGN KEY ( "fk_fustsiteid" ASC, "fk_fustid" ASC ) REFERENCES "dbo"."fust" ( "siteid", "id" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "gewas_fk_gewas" FOREIGN KEY ( "fk_gwsiteid" ASC, "fk_gwid" ASC ) REFERENCES "dbo"."gewas" ( "siteid", "id" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "groep_catgrpkey" FOREIGN KEY ( "objequ" ASC, "catcode" ASC, "grpcode" ASC ) REFERENCES "dbo"."groep" ( "objequ", "parentcat", "grpcode" ) ON UPDATE CASCADE ON DELETE SET DEFAULT CHECK ON COMMIT;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "gwblwijz_fk_gwblwijz" FOREIGN KEY ( "fk_gwblwid" ASC ) REFERENCES "dbo"."gwblwijz" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "gwkleur_fk_gwkleur" FOREIGN KEY ( "fk_kleurid" ASC ) REFERENCES "dbo"."gwkleur" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "locaties_fk_loc" FOREIGN KEY ( "fk_locid" ASC ) REFERENCES "dbo"."locaties" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "memo_sys_fk_memo_sys" FOREIGN KEY ( "fk_memo_sys_id" ASC ) REFERENCES "dbo"."memo_sys" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "privlab_fk_privlab" FOREIGN KEY ( "fk_privlab_id" ASC ) REFERENCES "dbo"."privlab" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "rellocatie_fk_rellocatie" FOREIGN KEY ( "fk_rellocatie_relnum" ASC ) REFERENCES "dbo"."rellocatie" ( "fk_relnum" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "vkbe_fk_vkbe" FOREIGN KEY ( "fk_vkbesiteid" ASC, "fk_vkbeid" ASC ) REFERENCES "dbo"."vkbe" ( "siteid", "id" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "vkbo_fk_vkbo" FOREIGN KEY ( "fk_vkbositeid" ASC, "fk_vkboid" ASC ) REFERENCES "dbo"."vkbo" ( "siteid", "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "vkdoc_fk_vkdoc" FOREIGN KEY ( "fk_vkdocsiteid" ASC, "fk_vkdocid" ASC ) REFERENCES "dbo"."vkdoc" ( "siteid", "id" ) ON DELETE CASCADE;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "vkofpresetdet_fk_vkofpresetdet" FOREIGN KEY ( "fk_vkofpresetdet_id" ASC ) REFERENCES "dbo"."vkofpresetdet" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD PRIMARY KEY ( "siteid" ASC, "id" ASC );
CREATE INDEX "vkreg_fk_memo_sys_id_edi" ON "dbo"."vkreg" ( "fk_memo_sys_id_edi" ASC ) IN "system";
CREATE INDEX "vkreg_fk_par" ON "dbo"."vkreg" ( "fk_par_id" ASC ) IN "system";
CREATE INDEX "vkreg_fk_vkmix" ON "dbo"."vkreg" ( "fk_vkmix_id" ASC ) IN "system";
CREATE INDEX "vkreg_ind_fk_artpres_schemes_id" ON "dbo"."vkreg" ( "fk_artpres_schemes_id" ASC ) IN "system";
CREATE INDEX "vkreg_ind_regstat_fk_artikel" ON "dbo"."vkreg" ( "regstat" ASC, "fk_artsiteid" ASC, "fk_artnum" ASC ) IN "system";
CREATE INDEX "vkreg_ind_vkbe_vkofreg" ON "dbo"."vkreg" ( "fk_vkbesiteid" ASC, "fk_vkbeid" ASC, "fk_vkofregsiteid" ASC, "fk_vkofregid" ASC ) IN "system";
CREATE INDEX "vkreg_ind_vkdoc_vkofreg" ON "dbo"."vkreg" ( "fk_vkdocsiteid" ASC, "fk_vkdocid" ASC, "fk_vkofregsiteid" ASC, "fk_vkofregid" ASC ) IN "system";
CREATE INDEX "vkreg_levkey" ON "dbo"."vkreg" ( "levcode" ASC ) IN "system";
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "vkresreg_fk_resreg" FOREIGN KEY ( "fk_resregsiteid" ASC, "fk_resregid" ASC ) REFERENCES "dbo"."vkresreg" ( "siteid", "id" );

[Former Member]

and part 2:

ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "art_concept_fk_art_concept" FOREIGN KEY ( "fk_art_concept_id" ASC ) REFERENCES "dbo"."art_concept" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "art_potm_fk_art_potm" FOREIGN KEY ( "fk_art_potm_id" ASC ) REFERENCES "dbo"."art_potm" ( "id" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "artikel_fk_art" FOREIGN KEY ( "fk_artsiteid" ASC, "fk_artnum" ASC ) REFERENCES "dbo"."artikel" ( "artsiteid", "artnum" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "artmix_fk_artmix" FOREIGN KEY ( "fk_artmix_id" ASC ) REFERENCES "dbo"."artmix" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "artpres_schemes_fk_artpres_schemes" FOREIGN KEY ( "fk_artpres_schemes_id" ASC ) REFERENCES "dbo"."artpres_schemes" ( "id" );
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "categorie_catkey" FOREIGN KEY ( "objequ" ASC, "catcode" ASC ) REFERENCES "dbo"."categorie" ( "objequ", "catcode" ) ON UPDATE CASCADE ON DELETE SET DEFAULT CHECK ON COMMIT;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "extom_fk_extom_off" FOREIGN KEY ( "fk_extomid_off" ASC ) REFERENCES "dbo"."extom" ( "id" ) ON DELETE SET NULL;
ALTER TABLE "dbo"."vkreg" ADD CONSTRAINT "fudrager_fk_fudrager" FOREIGN KEY ( "fk_fudrager_id" ASC ) REFERENCES "dbo"."fudrager" ( "id" );

[justin_willey · Accepted solution]

It does look odd, as you say. Just to confirm: is it correct that the values of fk_artpres_schemes_id that give slow and fast results vary from run to run, ie a value that is slow one time can be fast another?

Are you able to capture a graphical plan for a slow and a fast query and see if they are different? Posting plans here can be tricky - see this question for a work around: http://sqlanywhere-forum.sap.com/questions/28174 (basically you have to trick the site into thinking its an image file)

[Former Member · Accepted solution]

yep, the very same id (value for "fk_artpres_schemes_id") can run fast and slow. When I issue consequetive queries rougly one out of 10 runs slow, and then 10 run fast again.

also: I can't reproduce this in ISQL (issueing 30 queries, gives 30 fast responses (all zero or milliseconds, so really fast)

I'm calling these queries through odbc and I think this has something to do with it (too) ...

b.t.w. the plan looks identical for all queries (and as far as I can tell it uses the index, this is from the plan viewer :

"Index Only Retrieval Scan Scan vkreg using index artpres_schemes_fk_artpres_schemes"

[Breck_Carter · Accepted solution]

The "slow plan" may only used occasionally, and it may be necessary to capture the plan on-the-fly, multiple times, in order to see the "slow plan". Then you can compare it to the "fast plan" to see what bad choice(s) exist in the slow plan, and perhaps come up with some way to force SQL Anywhere to use the fast plan (e.g., a FORCE INDEX clause).

The Application Profiler / Database Profiler may be used for this process, or the technique starting at Step 8 in this blog post.

[justin_willey · Accepted solution]

Are you able to exclude your application from the equation, while still using ODBC - eg using MSQuery or any other ODBC connection client.

If it's always consistently good with dbisql, you seem to be largely ruling out the database engine itself.

Is anything else going on at the same time in the database? What isolation level does your application use?

[Breck_Carter · Accepted solution]

> If it's always consistently good with dbisql, you seem to be largely ruling out the database engine itself.

I disagree. Which plan is chosen sometimes depends on the "runtime context" which may be vastly different between the application and dbisql: current contents of the cache, etcetera... it is somewhat analogous to the observer effect in physics.

[justin_willey · Accepted solution]

But Franky is saying that the plan is always the same (and always fast from dbisql). If the plan varied between the slow and the fast runs then I'd agree that something was affecting the engine's choice of plan.

If it's only ever slow from the application, that seems to me like the place to start - I'm thinking in terms of some affecting the connection, its settings, locking etc. Also other activity causing ckeckpoints, I don't know how well Franky can isolate the issue from anything else going on?

[Former Member · Accepted solution]

I've diven into the application profiler (didn't know it contained that much detail ...):

the fast queries all have this plan:

( Plan [ (B) ]
  ( WorkTable 
    ( HashGroupBy 
      ( IndexScan vkreg artpres_schemes_fk_artpres_schemes[ 1 = CAST(CAST("dba"."DT_WeeksInWeekRange"(CAST(vkreg.week_vanaf AS int),CAST(vkreg.week_tm AS int),pi_SWeekno,pi_EWeekno) AS unsigned smallint) AS smallint) : 5% Guess ] )
    )
  )
)

and the slow ones all have this plan:

( Plan [ (B) ]
  ( WorkTable 
    ( HashGroupBy 
      ( TableScan vkreg[ pi_id IS NOT NULL : 94% Guess ][ vkreg.fk_artpres_schemes_id = pi_id : 0.073472% Column ][ 1 = CAST(CAST("dba"."DT_WeeksInWeekRange"(CAST(vkreg.week_vanaf AS int),CAST(vkreg.week_tm AS int),pi_SWeekno,pi_EWeekno) AS unsigned smallint) AS smallint) : 5% Guess ] )
    )
  )
)

[Former Member · Accepted solution]

btw: I run this on a test db, only one connection, no checkpoints interfering, actually nothing I can think off (but I'm no expert :-))

should I simply force the index and forget about it, or are the things to investigate?

[justin_willey · Accepted solution]

Breck was of course right! Franky has posted more info with different plans for fast and slow cases so the above is redundant!

[justin_willey · Accepted solution]

That's really useful it shows the problem clearly - the table scan. As Breck said something is occasionally making the database choose a bad plan. If this was a supported version then it be a matter of a support case to try to work out why, but since it isnt a work around getting the server to use the index will be the answer. See if FORCE INDEX does the trick.

[Former Member · Accepted solution]

I'll start a new question, for the forcing of an index 🙂

[Breck_Carter · Accepted solution]

One possible reason for a bad plan being used occasionally is there's a limit on the number of plans that are considered before one is picked, and at different times, those candidate plans may be considered in a different order, and the "good plan" may be further back in line.

In any event, it is far more likely that FORCE INDEX will help now, than waiting for an EBF 🙂

[VolkerBarth · Accepted solution]

In any event, it is far more likely that FORCE INDEX will help now, than waiting for an EBF 🙂

I'd dare to say this is true for supported versions, too:)

[justin_willey · Accepted solution]

I'd do the FORCE INDEX and assuming that works just make a note of it to re-check when you upgrade to a current version of SQLA. It's worth mentioning that there have been a lot of changes to query optimisation (for the good) in later versions, especially going from 11 > 12.