.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 with date range JOINs

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server


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

View Complete Post

More Related Resource Links

Date Range Problem

How would I go about returning the closest ReleaseDate to DateIn? The record I would like to return is (2010-07-18 00:00:00.000 2010-07-17 00:00:00.000) CREATE TABLE #test ( ID INT ,DateIn DATETIME ,ReleaseDate DATETIME ) INSERT INTO #test VALUES(1, '07/18/2010', '01/10/2010') INSERT INTO #test VALUES(2, '07/18/2010', '07/17/2010') SELECT * FROM #test WHERE CONVERT(CHAR(10), '07/17/2010', 101) <= CONVERT(CHAR(10), '07/18/2010', 101) AND CONVERT(CHAR(10), '07/18/2010', 101) <= CONVERT(CHAR(10), '01/10/2010', 101) DROP TABLE #test  

problem with date range between select Query in data adapter


hi all,

can any one help me out...why am getting "data type mis match error"

 DateTime dFromDate = dt_From.Value;
            dt_From.Value = DateTime.Parse(dFromDate.ToShortDateString());
            string From = dFromDate.ToShortDateString();

            DateTime dToDate = dt_To.Value;
            dt_To.Value = DateTime.Parse(dToDate.ToShortDateString());
            string To = dToDate.ToShortDateString();
            OleDbConnection cn = new OleDbConnection(ConfigurationSettings.AppSettings["connec"]);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            dataGridView1.DataSource = ds.Tables[0];

 Please..its Urgent!

ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

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

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

DataContractJsonSerializer: Problem when deserializing the Date

Sorry for cross-posting. I posted the same question on the Silverlight forum, but it has not received a single answer yet. The majority of folks there must be more interested in UI than in more fundamental stuff.     Hello everyone! Let's say we have the folllowing POCO classes: public class PocoObject : List<PocoPropertyData> {     public PocoObject() {}     public override string ToString()     {         var parts = new List<string>();         foreach (var pd in this)             parts.Add(string.Format("{{N=[{0}], Type=[{1}], V=[{2}]}}", pd.N, pd.V == null ? typeof(object) : pd.V.GetType(),  pd.V));         return string.Join(",", parts);     } } public class PocoPropertyData {     public string N { get; set; }  // the property name     public object V { get; set; }  // the property value } As you can see, the PocoPropertyData can hold value of any type. When I execute the following code in both .NET 4 application and Silverlight 4 application: ========================================================= var poco = new PocoObject {       { new PocoPropertyData { N = "DateTimeField", V =  Da

Need help in Date Range

HI Guys I need help to write a query I have a table Employee: empIDEmployeeName Task StartDt                                EndDt     1      xxx                  1    1950-01-01 00:00:00.000 1979-12-31 00:00:00.000     2     yyy                  2    1980-01-01 00:00:00.000 2009-06-30 00:00:00.000     3      aaa                 3    2009-06-30 00:00:00.000 2010-01-31 00:00:00.000     4     bbb                  4    2010-02-01 00:00:00.000 null when I pass one variable with date like 20070101 I need check the date ranges and get the exact record(this case 2nd records)

Performance problem when passing a "nullable parameter"

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

Question Regarding Date Range

I have a requirement to confirm how many employees were 'active' on a given date.  The employee table which I'm using contains the following fields (among others):  1.   Row_Start_Date (when an employee started with the company) 2.  Row_End_Date (when an employee terminated with the company (if applicable)) What would be the best way to design this?  For example, let's say an employee started on 1/1/2010 (Row_Start_Date) and is still employeed (Row_End_Date is null).  If an analyst wants to see how many employees were active on 5/1/2010, how do I design it so that this employee is counted.  If I build a date dimension off of Row_Start_Date, then this employee would be reflected on 1/1/2010, but not on 5/1/2010.  Hopefully this makes sense, and I appreciate any assistance/suggestions in advance... KoleKRS

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 casting date

I'm developing a Windows Forms app using VS 2008 with a SQL Server 2005 database. I've got search form where users can search for data by entering one or two criteria. One of these is a date which corresponds to a date field in my database. The user clicks on a button, which feeds the criteria into a stored procedure. The results are displayed in a datagridview control. While the functionality works well with the non-date criteria, whenever a date criteria is used on it's own a cast error is raised ('Conversion failed when converting datetime from character string.') I've tried lots of ways of casting the date (formatting using left, right and mid finctions, using the substring function etc) but still can't get it to work. Code and SQL are shown below. Does someone know where I've gone wrong? 'DateAssayRequested' is the date in question. <vb> 'set up and initialise connection conn = New SqlConnection(etc) comm = New SqlCommand comm.Connection = conn comm.CommandText = "SPSearch" comm.CommandType = CommandType.StoredProcedure 'create input parameters, set types and values param = comm.CreateParameter param.ParameterName = "@assayid" param.Direction = ParameterDirection.Input param.Value = txtLabNo.Text

Date validation problem modelstate.isvalid is false

In my view i am formatting the date as follows<%= Html.TextBox("MemoDate", string.Format("{0:dd/MM/yyyy hh:mm}", DateTime.Now),new{ @readonly = true })%> In controller ModelState.IsValid become false as my current culture culture {en-US} doesn't match with the format of date.

Problem with date values in data-driven subscription reports

Hello together, I have a question in regards of data-driven subscription report. I already have an ad-hoc report in place which can be customized by date etc. I now want to run these reports with a subscription. My challenge is now that I don't know how I can pre-define the date. I basically want the start date of NOW() and the end date 7 days in the past. I was looking around but did not get any clue where and how I have to configure it the way it works. I found this thread but honestly do not understand it well... :( http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fb48fd15-09dd-4f5f-a09f-d92bf304c1a2 Can someone help me out please? M.

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

Problem displaying Date from Sql table on Calendar Control

Hi, I am displaying Event dates from SQL table on Calendar Control in ASP.NET. Also, I have GridView Control which shows event details when certain date is clicked on calendar control. But I have a problem as not all dates are displaying properly. Strangly enough only dates which have same month and day date are displayed. For example: It shows ok dates such as 08/08/2010, 09/09/2010, 10/10/2010 etc. If I click on the date which in SQL table exists as 11/25/2010 or 12/15/2010 etc (no matching month/day numbers) it shows error message saying: "System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string."  Follwoing is my code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.Sql; using System.Runtime.Remoting.Messaging; using System.Configuration; using System.Data.SqlClient; using System.Data; using System.Drawing; public partial class Test_Calendar : System.Web.UI.Page { SqlConnection mycn; SqlDataAdapter myda; DataSet ds = new DataSet(); DataSet dsSelDate; String strConn; private void Page_Load(object sender, System.EventArgs e) { strConn = "Data Source=mydatasource;Initial Catalog=DBname;Persist Security I

Date formate problem

Hi I have faced one problem with processing date field from xml file. i have read RSS site and get date field from this site and parse that date field using below code i got pubDate field from xml like : <pubDate >06 Sept 2010 09:15:01 +0300</pubDate > datefield = DateTime.Parse(xmlfile.Element("pubDate").Value.Replace("\n\r", "").Replace("\r\n", "").Replace("\n", "").Replace(" Sept "," Sep ").Trim(), FinishCulture.DateTimeFormat, DateTimeStyles.AdjustToUniversal).AddHours(3).ToString("dd.MM.yyyy HH:mm:ss"),   I have face this problem because of month format "Sept". i have also implement this functionality with other RSS site they working fine with above code as they return date like : <pubDate >06 Sep 2010 09:15:01 +0300</pubDate > So, the problem is with month name Sept - Sep. can you please tell me how can i handle "Sept" month name format.   Thanks Nikunj Padaliya  
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