3

I am having a table having 360369 no of rows. I am using SQL SERVER.

Today when i tried a simple select statement with cfquery and blockfactor,I did not find any performance enhancement.

Moreover the performance reduced.

  1. Without blockfactor , the execution time was 120884.

  2. With blockfactor, the execution time became 144003.

As per the definition of blockfactor, my maximum row size was 437bytes ,so

blockfactor = 32k/437 = 32768/437 = 74.9

So I have used the blockfactor as 74 or 75.

But I did not find any performance enhancement.

Deepak Kumar Padhy
  • 4,128
  • 6
  • 43
  • 79
  • 1
    Check this blog http://www.bennadel.com/blog/338-ColdFusion-CFQuery-BlockFactor-Is-Not-Impressing-Me.htm it may give you some clues. – Imane Fateh Jul 01 '13 at 11:07
  • Yes already checked,i also found the same issue no performance enhancement with blockfactor. – Deepak Kumar Padhy Jul 01 '13 at 11:11
  • How much time have you run this query? you don't always see the speed difference in a query if you only run it once... Which version of SQL server do you have? which driver do you use to get connected to your database? – Imane Fateh Jul 01 '13 at 11:16
  • 1. I have executed for around 5-10 times. 2. MS SQL SERVER 2008 3.Mircrosoft SQL SERVER – Deepak Kumar Padhy Jul 01 '13 at 11:19
  • @DeepakKumarPadhy according to cf documentation it might not be supported by some database check documentation on http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html – Keshav jha Jul 01 '13 at 12:23
  • yes that might be the reason, in some other blogs also i found 'blockfactor' is mainly supported by Oracle. – Deepak Kumar Padhy Jul 01 '13 at 13:32
  • 1
    I have never really used it, but I remember the [same question](http://forums.adobe.com/thread/251006) came up a few years ago. From my limited tests, that setting seemed to control the [fetchSize](http://msdn.microsoft.com/en-us/library/ms378591%28v=SQL.90%29.aspx) hint, and it only had an affect when using `selectMethod=cursor` (server side cursor) in the dsn. See [Set a default row prefetch in SQL Server using JDBC driver](http://stackoverflow.com/questions/15074037/set-a-default-row-prefetch-in-sql-server-using-jdbc-driver) – Leigh Jul 01 '13 at 15:59

1 Answers1

0

I'd start changing that when you're seeing a build-up of wait time (bottle neck) for SQL server to answer the request over the network cards. If your network cards are "idle" you'd not see a difference

Community
  • 1
  • 1
Walter Verhoeven
  • 213
  • 1
  • 6