.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Performance problem when passing a "nullable parameter"

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
Say @startDate and @endDate are my datetime parameters. If users does not want to limit the @startDate for example, he/she sends a null value to @startDate. Same goes for @endDate (and any other parameter). What I like doing: Select * From MyTable Where MyTable.Date >= IsNull(@startDate, MyTable.Date) And MyTable.Date <= IsNull(@endDate, MyTable.Date) The above looks nice as far as coding but when I look at the execution plan I'm surprised to see the my clustered index (that has it's first column as "MyTable.Date") is not used. instead I get an index scan. More frustrating is that the following works with an index seek and is much faster: Select * From MyTable Where MyTable.Date >= @startDate And MyTable.Date <= @endDate So what should I do? am I doing something wrong? I don't want to use dynamic sql because it's generally slower and prone to sql injection (is dynamic sql my only choise)? I don't want to use "if statements" because then i'd have to rewrite my code several times (in this case three combinations but some of my procedures have up to 8 "nullable" parameters). Or maybe there is another way of implementing what I call here "nullable parameters". obviously I preformance is crucial. thanks, Dror

View Complete Post

More Related Resource Links

passing object values from server to client problem


I get a message that x_login is not declared.  I added Dim login as String = "123", but that didn't help.  What's the problem?

code behind....

Dim Login = "123"
x_login.Value = loginID


<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<form id="simForm" runat="server" method='post' action='https://test.authorize.net/gateway/transact.dll'>
        <input id="HiddenValue" type="hidden" value="Initial Value" runat="server" />
        <input type='hidden' runat="server" name='x_login' id='x_login' />
        <input type='submit' runat="server" id='buttonLabel' />



Problem with passing an Image as binary through web service



I have a web service that is accessed by a windows forms application and receives updates like facility name, address, image etc. Everything works fine except the image data is not being passed to the windows app. I am saving the images in database on both sides, so when I pull it from the server I use the following code

if (!reader.IsDBNull(floorPlanOrdinal))

facility.FloorPlan = (byte[])reader["FloorPlan"];

I can see the facility.FloorPlan Dimentions as 103840 when I debug this method which means that the data is pulled from the database fine.

On the forms side though the dimentions become 0, all other values pass fine except this. Here is the facility property in the web service on server side.

using System;

using System

Wickedly Strange .Net WPF Performance Problem Dissapeers with Windows 7 Performance Monitor Open

Hello Everyone, I have been developing one large and complex WPF application for 13 months now, all is going well and especially tonight as I now have some sort of clue or hint to what I have been seing now for quite some time.  Here is the rundown. 1) Running on i5 processor with 4GB RAM and Windows 7 2) The application was developed all in .Net 4.0, WPF C#. 3) It is heavily GUI intensive and also uses Entity Framework Detached for Database Access. 4) Is multi-threaded / multi-tasking. I am a nutcase when it comes to testing.  Since my graphical application heavily depends upon performance and determinsm (please somebody don't start with the C++ vs .Net on here) and has been performing great FOR THE MOST PART.  Every once in a while, I see that the application runs into the weeds for 5 or 6 seconds and the interface is unresponsive.  I have this thing tuned finely too.  Well now toward the end of development I have finally had to face that bug so to speak.  I can force it by clicking spastically on my buttons causing an event storm and making the system update the GUI.  SO!  Great start, I can now cause the problem on a regular basis (for you youngsters that is actually a good thing)!  Alright, so I start with the easiest investigative tool, CNTRL - ALT - DEL into Windows 7 Performance Monitor.  As you can tell from

Problem passing custom object returned by the same webservice among two separate projects

I am running into casting issue with this scenario: 1. I made a custom object lets call it EmployeeObject.  2. The Employee object is wrapped in the EmployeeWebservice.  3. I have a client application project lets call it EmployeeClientApplication and a class library called DoSomeWorkClassLibrary.  Both EmployeeClientApplication and DoSomeWorkClassLibrary reference the EmployeeWebService and need to be able to pass the EmployeeObject back and forth.  4. The EmployeeClientApplication also references the DoSomeWorkClassLibrary. 5. The employeeObject in the EmployeeClientApplication becomes EmployeeClientApplication.EmployeeWebService.EmployeeObject. 6. The employeeObject in the DoSomeWorkClassLibrary becomes DoSomeWorkClassLibrary.EmployeeWebService.EmployeeObject. 7. The DoSomeWorkClassLibrary has a method called DoSomeWork that takes an EmployeeObject. When I try to pass employeeObject from EmployeeClientApplication to DoSomeWorkClassLibrary by doing something like this: DoSomeWorkClassLibrary.DoSomeWork(EmployeeClientApplication.EmployeeWebService.EmployeeObject), I am getting Unable to convert EmployeeClientApplication.EmployeeWebService.EmployeeObject to DoSomeWorkClassLibrary.EmployeeWebService.EmployeeObject.  I have searched high and low to fix this issue, I have tried changing the namespace of the reference.cs file of the webservice in

SQL performance problem

I have a production database replicated on my local machine.  I run a select query for 10k rows.  On my local box the query takes less than half a second.  On production the query takes a full 4 seconds.  I have looked at the SQL Server Profiler and I see a lot of activity on production.  I would like to pinpoint exactly what or what group of items is causing the performance to degrade on production.  What are your suggestions? Local Environment:  SQL 2008, 32bit quad-core processor, 4gb ram Production:             SQL 2000, 32bit 8 processor cores, 16gb ram BrianMackey.NET

Checkpointing and IO performance problem

Hi guys, Just a quick question regarding a problem i seem to be having at the moment which i can't seem to get my head around. I have started at an organisation which has a very basic setup. A clustered sql 2000 sp4 server with approx 90 databases on. Only a few of these have much traffic on and performance on the whole seems ok. The server is connected to a SAN with two LUNS being made available for the data and log drives. Performance in general is ok with disk latency for reads and writes on both luns around 10-20ms. There is a constant stream of reads using approx 1.5 - 2 mb per second from the data drive. On the odd instance disk read go upto 20-30mb per second and the disk queue may rise to 10 for a very short period of time but on the whole the disks seam to handle it and latency for the above peak may go upto 200ms. The problem seems to happen when checkpointing seems to happen. I have set the trace flags so i can see what database is checkpointing at any given time and when the highest throughput database checkpoints we see performance problems. Latency can go to 20-30 seconds for 30-60 seconds which effectively halts processing for this time. Errors start appearing in the sql log for files taking longer than 15s to respond. What's throwing me is that the throughput on the disks doesn't seem very high at these points. As an example checkpoint pages

problem with passing parameter values to stored proceedure from asp.net c#

hi all, i am facing a problem while passing parameters to a stored proceedure. following is my sql query written for creating this stored procedure:   CREATE proc [dbo].[getstudent]( @wherecolumn nvarchar(2000), @wherevalue nvarchar(2000)) asbegin  declare @sql nvarchar(max) set @sql = 'select * from student2 where ' + @wherecolumn+'='+@wherevalueexec sp_Executesql @sql end   my table contains four columns: And the sql Query For the table is as follows:   CREATE TABLE [dbo].[student2]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [rollno] [int] NOT NULL, [class] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_student2] PRIMARY KEY CLUSTERED ( [rollno] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]   from page behind I am passing the values to parameter as follows:   SqlCommand command = new SqlCommand("getstudent", c.con); SqlDataAdapter datadapter = new SqlDataAdapter(c.cmd); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@wherecolumn", SqlDbType.NVarChar).Value = "rollno"; command.Parameters.Add("@wherevalue", Sq

Problem passing a text box value from aspx.cs page to SelectParameters in aspx page

Hi,I would like to take a value from a text box and pass it as a search parameter to my database. I get the value from my code behind page as follows: protected void btn_click(Object sender, ImageClickEventArgs e)      {           AccessDataSource1.SelectParameters["inputValue"].DefaultValue = TextBox1.Text;           string test = AccessDataSource1.SelectParameters["inputValue"].DefaultValue;                      //string inputValue = TextBox1.Text;          //Response.Write(inputValue);         // AccessDataSource1.SelectParameters.Add("@name", inputValue);          //Response.Write(@inputValue);                              } protected void btn_click(Object sender, ImageClickEventArgs e)     {          AccessDataSource1.SelectParameters["inputValue"].DefaultValue = TextBox1.Text;          //An alternate option I have used is          //string inputValue = TextBox1.Text;         // AccessDataSource1.Selec

Problem : Need to make COM InterOp at runtime using reflections Passing Pointers as parameters?

Hello, I need to make COM IntetrOp at runtime using reflections. My native COM Object's exposed methods have some parameters as pointers (DWORD*) and some double pointers (DWORD**) and some are user defined types(e.g SomeUDTType objSmeUDTType) and vice versa its pointer(i.e. SomeUDTType *pSomeUDTType). Now for dynamic method invocation, we have single option for passing parameters as array of object i.e object[] and filling this array statically. But I need to pass pointers and references and pointers to pointers. For now how can I be able to populate object array as mixed data of simple data types, pointers or references and pointers to pointers. Working Example: Native COM exposed method : STDMETHODIMP MyCallableMethod(DWORD *value_1,BSTR *bstrName,WESContext a_wesContex Translated by tlbimp.exe (COMInterop) UDTINIDLLib.RuntimeCallingClass.MyCallableMethod(ref uint, ref string, UDTINIDLLib Now calling these methods at runtime using reflection at runtime, See here :        Assembly asembly = Assembly.LoadFrom("E:\\UDTInIDL\\Debug\\UDTINIDLLib.dll");         Type[] types = asembly.GetTypes();         Type type = null;    //foreach (Type oType in types)         {             try      &nb

Performance problem when rendering in PDF, printing (SSRS2008)

Hi I have a report containing a rectangle/tablix attached to a dataset of course details. The rectange contains a number of text boxes showing course details (fields from the dataset: course title, department etc.) and a sub-report which is a list of students on that course (controlled by passing appropriate parameters). This worked fine until I decided I wanted one of the text boxes (course_title) to repeat on each page whilst printing. To achieve this I added a column group on course_title and set the header to repeat on each page, as suggested elsewhere on this forum. This works fine when simply viewing the report in BIDS or after deployment (SharePoint), but not when attempting to print, print preview or convert to another format (e.g. pdf). The report itself runs in a matter of seconds (<10) but the conversion runs for about 90 seconds before aborting (probably due to server settings) and in IE taking the browser down with it. I've tried not repeating the group header on each page but the problem persists, unless I remove the group altogether. I've also tried adding the student dataset and the list directly into the main report, but that created too many restrictions on the list (e.g. not enabling list column headers to be repeated on each page). Is there a known issues with grouping affecting the performance in this way? Are there other ways around any of these p

SSRS problem with passing multiple parameter values via URL %2c

Hi, I have a problem with passing multiple parameter values from one report (A) to another report (B).   For example I built a URL string in report (A) with the parameters like &Parameter1=1&Parameter1=2. As soon as I click on that URL the URL parameters look like &Parameter1=1%2c2 and the parameters don’t getting populated on report (B).   If I change the parameter in the URL to &Parameter1=1&Parameter1=2 and execute the URL manual the parameter on report (B) getting populated properly.   Is there a way to disable the coding like %2c (comma) or is there another solution for it?   Thanks

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

problem when passing data from view back to controller?


I have following situation - I am pasing user info object from Controller to View. It contains GUID UserID,  which i dont want to be seen on page.

So I  removed every   Html.LabelFor(model => model.UserID), Html.TextBoxFor(model => model.UserID) etc... from generated View source.

And because of this when Html.BeginForm() returns that object back to Controller  all values is there but UserID is lost??

If I leave Html.LabelFor(model => model.UserID), Html.TextBoxFor(model => model.UserID) etc.. in View everything is fine.

But I dont want to show UserID?

Where is the problem here?

                <%= Html.LabelFor(model => model.C__User_Id) %>

Performance problem with date range JOINs



when I am joining two tables where one table has two columns which specify a date range and the other table has one column with a single date which must be in that range for a join, then the performance is not so hot. The T-SQL example only shows the basic query scheme, in reality there are appropriate indexes (but not on the date columns since I found them not helpful) and the DateRange column has about 100 mio rows and the Incident table about 200,000 rows. The query currently takes hours, I must speed it up by at least factor 10.

TIA for any ideas. Regards

	[DateRangeID] [int] NOT NULL PRIMARY KEY,
	[RangeTypeID] [smallint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL)

	[IncidentID] [int] NOT NU

problem in passing string array from activex dll to JavaScript


I do have a problem in passing string array from activex dll to JavaScript. i need to implement a methos as 

C# : 

Problem Using Invoke and passing multiple byref parameters


I have code, shown below highly simplified that works all except for 1 thing: The variables being passed byRef get passed, but once modified in RecordData, they are never updated in the calling function. The calling function does not receive the updated data that is in the variables custid and amt. When debugging, I see the data in the else section of if me.invokerequired, but once it returns from the callback and is in the latter part of if me.invokerequired the data is missing.

How do I fix this?


Delegate Sub RecordDataCallback(ByRef custid As String, ByRef amt As Double)

Private Sub RecordData(ByRef custid As String, ByRef amt As Double)
    If Me.InvokeRequired Then

Problem passing parameters to controller


I have a problem with getting a parameter in my controller.
My url is: http://localhost:portnr/News/Details/43

My Global.asax:

    null, // Route name 
    "{controller}/{action}/{newsid}", // URL with parameters 
    new { controller = "News", action = "Details", newsid = "" } // Parameter defaults 

My controller code:

public class NewsController : Controller 
    public ActionResult Details(int? newsid) 
        //some code 

My controller is never getting the value out the url, how can i fix it ?
I don't want to use: /Details?newsid=43

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend