Impact of SELECTed colums on the execution plan

Hi guys,

I have something weird that I want to understand.

I have a big table, containing around 17 millions of rows. This table has been progressively indexed over time, mainly by following some of the recommendations of the DB tuning advisor. As a result, we have around ten indexes on this table, some of them are using the new "INCLUDE" feature of SQL2005 indexes with non-key data.

The problem I have is the following : I have a very simple query (SELECT <some columns> FROM that_table WHERE <simple clause conditions>) which should benefit from the indexes built on the table. But depending on the columns I select, the execution plan varies totally !!

With one column selected, the good index is used, and the query is fast.

With two columns selected, the execution plan becomes complicated (several different indexes seeked in parallel). I guess the optimization system is trying to get benefit from some non-key data included in some of the indexes ? But I'm not sure...

With a "SELECT *" no appropriate index is used. The excution plan shows it will do a "clustered index scan" over the primary key (which is not part of the where clause), resulting in a full scan of the table... OK, I know that "SELECT *" is not a good practice, but anyway, this result is very surprising.

(I tried to make it short, I can detail the queries and the construction of the indexes if needed, but the main info is there I think)

How can it be possible to have so many differences, just by changing the list of SELECTed columns ? It's beyond my understanding of what is an index...

Thanks for your help

Mathieu

Hi Mathieu,

There's a few things that could be going on here, and it's hard to make a definitive call without investigating the query plan, but...

If you include columns in the select list that are not served by the index the used in order to serve the where clause, the engine will need to perform what's known as a bookmark lookup in order to retrieve the column values that are not included in the index. It of course gets a bit more complicated if the optimiser chooses parallel indexes.

So, if your query was something like:

SELECT id, name, dob

FROM tblPerson

WHERE id = 993

You can also try execting the query with OPTION (MAXDOP 1) in order to determine if the generation of a parallel query plan is introducing unnecessary overhead.

You would create an index on the id column and list name and dob columns in the new INCLUDE clause. This would be a good starting point, but you'd need to take into account all other queries against this table in order to decide if indeed this. Indexing is a huge subject, so get googling! :)

Cheers,

Rob

|||

To be clearer :

1st case :

SELECT a FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

c AND d are covered by an index, and the column a is included (INCLUDE keyword) in that index. b, c, and d are regulars data columns (not involved in PK). The exec plan is fine, the query is fast.

2nd case : I just add a column in the SELECT, the where clause is left untouched

SELECT a, e FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

Here, the column e is not included in the index mentionned hereunder. The exec plan becomes complicated, involving others indexes in parallel. The query becomes slow.

3rd case : retrieving all columns, the where clause is still left untouched

SELECT * FROM table WHERE b=constant AND c>constant AND d LIKE 'string%'.

This time, the exec plan is "clustered index scan" over the PK. But the PK does not appear in the where clause ! Result is a full scan of the 17millions of rows... catastrophic !

While writing this post, I'm getting convinced that SQL Server is perturbed by the INCLUDEd columns in the indexes... What's your opinion ?

|||

Hi Mathieu,

As mentioned, if a column in the select list is not included in the index, a bookmark lookup is used. So, in the 2nd case above, you would either include column e in the index used for the operation either as a key value or in the INCLUDE list.

In the 3rd case, it would appear the optimiser has decided that rather then performing lookups for all the columns not servicable by the index (ie the SELECT *), it has chosen to perform a full scan. I'd need to see the full query plan and the schema to provide more info, but it sounds like you need to revisit your indexing strategy from the ground up.

Although INCLUDE is new to 2005, I've never had nor heard of an issue directly related to its use (yet).

Cheers,

Rob

Images From Sql Database

I have a database in sql server with a table containing a few images stored as binary data and there unique ids. From a Visual Basic.NET Application I want to use the crystal report to display 1 of the images based on the id that it is fed. How can I read and image from the sql database and display it in a Crystal Report? Any help offered is greatly appreciated.I've used a stored procedure to get the images from db and then add the blob field to the report. problem is that the wizard "enter parameter value" keeps prompting while you're going to drag the blob to your report. I still haven't find a solution for this problem.|||Just drag the blob column to report and go to preview mode and check if it is displaying the image|||I too have the same problem But in the case of Auto cad images(*.dwg)

I am saving the image in image field (SQL server) in binary format

Is there any solution for that?

Reply as much as earlier!|||What is the error you get?|||While adding the image field(SQL server) in crystal report,it doesn't showing any error

But

1.If the image format other than autocad(*.dwg) then it is showing the image as it is.

2.But in the case of autocad image it doesn't showing anything the field space would be blank.

If anyone guide me for this prob,please help me out

My project has been delayed because of this problem

Lingeswaran.r|||Hi all
I have some problem load image to Crytal Report 11

I have database from sql Server. The Columns Images after.
The column
Column name Data Type Length Allownulls
Picture varchar 50 *

After i save to database is

image\picture.jpg.

When i drag the column to Crystal Report 11 it have only is image\picture.jpg.
Can you help me ?
Display the picture.jpg is a picture
Thank you.|||Hi,
I had face the problem in Sql server with CrystalReports11 at Visual studio2005. By doing the following way you can solve the problem.

Let assume table "Item_t" having "ItemPicture'' column and its datatype is "Image'' and assume picture is stored in that table.

fecth the picture data to a datatable.

Let assume the picture is in dtItem & write the following method

private DataTable ConvertPicture(DataTable dtItem)
{
DataTable dtReturn = dtItem.Clone();

foreach (DataRow dRow in dtItem.Rows)
{
DataRow drRow = dtReturn.NewRow();

// Convert the picture into binary format

Byte[] bPictureInByte = (Byte[])dtItem.Row["ItemPicture''];
System.IO.MemoryStream mStream = new System.IO.MemoryStream(bPictureInByte ); // convert as stream
Bitmap pictureBimap= new Bitmap(mStream);//store in a Bitmap

// save the picture data in Local harddisk as .jpg file
pictureBimap.Save("C:\\temp\\Itempicture.Jpg", System.Drawing.Imaging.ImageFormat.Jpeg);

//Read the same jpg Picture from your Local harddisk
System.IO.FileStream fs = new System.IO.FileStream("C:\\temp\\Itempicture.Jpg", System.IO.FileMode.Open);
System.IO.BinaryReader br = new System.IO.BinaryReader(fs);

//convert to byte[] & save in your new data row
drRow = br.ReadBytes((int)br.BaseStream.Length);
br.Close();

//add the datarow to the datatable dtReturn;

dtReturn.Rows.add(drRow);

}

return dtReturn

}

now you passing the dtReturn data table to your .rpt file and print the Image.

Best wishes|||But now i don't want to Write a picture into the SQL Server
I only want write a link of it.
Example: image\picture_name.jpg
After that i load into Crytal Report.
How I can to do it ?
You can help me ?

If i write to sql server is no proplem.|||Hi all,
I want to display image in report but I can't. The following is what I do.
I create a report in C#.NET 2003, using CR XI. Data is stored in SQL Server 2005, and I store image as BLOB field. I design by drag and drop fields into report. In my code, I connect to database, get mydataset by executing a sql string, and then using rpt.SetDataSource(mydataset). All data in text field is ok but image field is empty.
Anybody help me, please.|||I know why I can't display image in report now. A simple reason is the aslias name of image field in sql statement and the image field name in my report are different.

But now I don't understand the reason why sometimes I can 't drag image field from tree view into report. Anybody know?

Image Data

What is the correct (or best way) to
Insert Into an Image data field.
I have an app that displays records containing Equipment Pictures.
I am able to display the record and display the picture.
I want to allow the user to add an new record and also insert a
new picture associated with the record.
The table was imported so the existing pictures came in fine
but I now need to build the app to facilitate this needed
functionality.
any help is much appreciated.
thanks in advance,
bob mcclellanYou can store the physical path of the pictures in one column and it is
better to hide that file
Madhivanan|||One of the option is this script wriiten by (If I remember well) Dan Guzman
Dim ADOCmd As New ADODB.Command
Dim ADOprm As New ADODB.Parameter
Dim ADOcon As ADODB.Connection
Dim intFile As Integer
Dim ImgBuff() As Byte
Dim ImgLen As Long
Set ADOcon = New ADODB.Connection
With ADOcon
.Provider = "MSDASQL"
.CursorLocation = adUseClient
.ConnectionString = "driver=
{SQL Server};server=(local);uid=<username>;pwd=<strong
password>;database=pubs"
.Open
End With
'Change this to the path of a GIF file you want to use for testing.
IMG_FILE_GIF = "E:\Graphics\GIF\Image.gif"
'Read/Store GIF file in ByteArray
intFile = FreeFile
Open IMG_FILE_GIF For Binary As #intFile
ImgLen = LOF(intFile)
ReDim ImgBuff(ImgLen) As Byte
Get #intFile, , ImgBuff()
Close #intFile
Set ADOCmd.ActiveConnection = ADOcon
ADOCmd.CommandType = adCmdStoredProc
ADOCmd.CommandText = "uspInsertBLOB"
Set ADOprm = ADOCmd.CreateParameter(, adChar, adParamInput, 1, "1")
ADOCmd.Parameters.Append ADOprm
'The datatype must be specified as adLongVarBinary
'For the code to function correctly comment this line.
Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
adParamInput, ImgLen)
'Uncomment this line.
'Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
adParamInput, (ImgLen + 1))
ADOCmd.Parameters.Append ADOprm
'Set the Value of the parameter with the AppendChunk method.
ADOprm.AppendChunk ImgBuff()
'The preceding example assumes you are using a small image file.
'See the article reference in the REFERENCES section for handling a
'large image file.
ADOCmd.Execute
Set ADOCmd = Nothing
Set ADOprm = Nothing
"John316" <bobmcc@.tricoequipment.com> wrote in message
news:O5XzhRmHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> What is the correct (or best way) to
> Insert Into an Image data field.
> I have an app that displays records containing Equipment Pictures.
> I am able to display the record and display the picture.
> I want to allow the user to add an new record and also insert a
> new picture associated with the record.
> The table was imported so the existing pictures came in fine
> but I now need to build the app to facilitate this needed
> functionality.
> any help is much appreciated.
> thanks in advance,
> bob mcclellan
>|||Thanks Uri !
I will try this out.
thanks much for the reply.
It is much appreciated.
bob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O89CQdmHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> One of the option is this script wriiten by (If I remember well) Dan
> Guzman
> Dim ADOCmd As New ADODB.Command
> Dim ADOprm As New ADODB.Parameter
> Dim ADOcon As ADODB.Connection
> Dim intFile As Integer
> Dim ImgBuff() As Byte
> Dim ImgLen As Long
> Set ADOcon = New ADODB.Connection
> With ADOcon
> .Provider = "MSDASQL"
> .CursorLocation = adUseClient
> .ConnectionString = "driver=
> {SQL Server};server=(local);uid=<username>;pwd=<strong
> password>;database=pubs"
> .Open
> End With
> 'Change this to the path of a GIF file you want to use for testing.
> IMG_FILE_GIF = "E:\Graphics\GIF\Image.gif"
> 'Read/Store GIF file in ByteArray
> intFile = FreeFile
> Open IMG_FILE_GIF For Binary As #intFile
> ImgLen = LOF(intFile)
> ReDim ImgBuff(ImgLen) As Byte
> Get #intFile, , ImgBuff()
> Close #intFile
> Set ADOCmd.ActiveConnection = ADOcon
> ADOCmd.CommandType = adCmdStoredProc
> ADOCmd.CommandText = "uspInsertBLOB"
> Set ADOprm = ADOCmd.CreateParameter(, adChar, adParamInput, 1, "1")
> ADOCmd.Parameters.Append ADOprm
> 'The datatype must be specified as adLongVarBinary
> 'For the code to function correctly comment this line.
> Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
> adParamInput, ImgLen)
> 'Uncomment this line.
> 'Set ADOprm = ADOCmd.CreateParameter(, adLongVarBinary, _
> adParamInput, (ImgLen + 1))
> ADOCmd.Parameters.Append ADOprm
> 'Set the Value of the parameter with the AppendChunk method.
> ADOprm.AppendChunk ImgBuff()
> 'The preceding example assumes you are using a small image file.
> 'See the article reference in the REFERENCES section for handling a
> 'large image file.
> ADOCmd.Execute
> Set ADOCmd = Nothing
> Set ADOprm = Nothing
>
> "John316" <bobmcc@.tricoequipment.com> wrote in message
> news:O5XzhRmHFHA.3588@.TK2MSFTNGP14.phx.gbl...
>