1

I've a performance issue with using Scalar User Defined Functions(UDF) in queries.
There is a UDF fn_get(i int) which returns a scalar.. It holds lot of logic and performs normal scalar operations..

Actually
SELECT *,fn_get(i) FROM #temp1;
is fetching 10,000 results within 3 seconds and getting displayed in Studio UI.

Whereas,
SELECT *,fn_get(i) INTO #temp2 FROM #temp1;
is inserting the same 10,000 results into table #temp2, by taking time of >4 minutes

Don't know why difference is such enormous (3 seconds vs 4 minutes :O)
Am not sure, if this is the way to ask a question here.. Any guidance to improve the query performance is of great help..

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
rudebutgood
  • 43
  • 1
  • 6

2 Answers2

1

Michael is correct, if the table is very wide I would expect a massive performance hit on inserting the entire table width l, so your first step should be to only select/insert the int value and see what the performance looks like.

After that, the other thing I'd like you to try is switching your UDF to one that is very simple, (maybe just multiply by 10) and see if performs just as slowly.

I ask you to test that because one interesting thing I have experienced with UDFs in sybase IQ is if you use an operation that is not supported by IQ but is supported by the ASA store, is that you will cross the engine boundary. This could also happen if you created your UDF "in system" which means it's in the ASA store. If your #temp2 table is in the IQ store, the data movement would be read from IQ, moved to ASA to perform data ops then finally move back to IQ (slowly) to write to your temp table. In my experience, data moves very quickly from the IQ engine to the ASA engine, but much much slower going the other direction.

This is why I believe the select was quick (it came directly from the ASA store after data ops) and the insert is almost 100x slower.

Hotel
  • 1,361
  • 11
  • 13
0

Sybase IQ is a OLAP tuned columnar database. Which means, out of the box it is tuned for reads, not writes. So it would be normal for read performance to greatly outpace write performance, even on the same data set.

Now many things can affect write performance, storage type, IO bandwidth, caching, indexing are a few of the factors.

To get more detailed information on the particulars of your query, you should take a look at the execution plan. This will help break down where the system is spending time.

SAP has a detailed document on Sybase IQ execution/query plans. It may not be for the specific version of IQ you are running, but the information will be generally applicable.

Note: It is highly discouraged to use select * (ever) in a columnar database. The data is split and organized by column, so reassembling an entire row is a very costly procedure. Unless you absolutely need every column in the row, you should always specify which columns. It is also just general SQL best practice to always specify columns in your query, even if you are retrieving all of the columns.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34