.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

optimizing a query / a table

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

i have a table T with (simplified)schema: 
T(Type, Interval, a, b, c, d). all the fields have int type. there is no primary key and no index set on the table and it has about 13 million records (the actual table contains 9 more int fields).

i wrote a query that counts how many distinct d values appear for each triple (a,b,c), with Type = typeValue and Interval between I1 and I2 like this:

SELECT a,b,c, COUNT(d) AS NoOfDValues
SELECT a, b, c, d FROM T
WHERE Interval BETWEEN I1 AND I2 AND Type = typeValue
GROUP BY a,b,c,d
) AS Q1
GROUP BY Q1.a, Q1.b, Q1.c

for the data in my table, this query is executed in about 35 seconds. and i need to run it thousands of time from my app.

how can i optimize both my table (only with indexes) and my query? 

thank you


update. i tried some indexes as suggested here, but the best execution time i get for the worst case scenario is 15 seconds. is there a way to make this query run in at most 2 or 3 seconds?

View Complete Post

More Related Resource Links

lookup table query


we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

Need help in optimizing following query.

Need help in optimizing following query. I am having long delay if i am opening the commented lines in either of inner or outer query. SELECT X.* FROM ( SELECT CON_SHIPPER_CONTENT_ID ,CON_SA_CREATE_DATETIME ,CON_PK_JOB_ID ,CON_PK_DATABASE_CONTAINER_ID ,CON_MAILCLASS ,CON_PROCESSINGCATEGORY ,CON_ENTRY_POINT_PHYSICAL ,VerificationLocation ,CON_ZONE_SKIPPING_INDICATOR ,PriorityMailIndicator ,CON_NEWS_INDICATOR ,CON_DESTINATION_DISCOUNT_INDICATOR ,CON_RATETYPE ,CON_SCHEDULED_INDUCTION_DATE ,CON_INHOME_START_DATETIME FROM MDB_CONTENT_INFO WHERE ((CON_CONSIGNEE_ID IS NULL) OR (CON_CONSIGNEE_ID = 'N/A')) AND ((CON_FAST_CONTENT_ID IS NULL) OR (CON_FAST_CONTENT_ID = 'N/A')) AND (SAC_IsLinked is null or SAC_IsLinked = 'False' ) AND (SAC_SchedulerContentID is null) AND ShipperApptRequestID is null AND (CON_SHIPPER_CONTENT_ID not in (SELECT items FROM dbo.GetCommaSplitValue(@InputVariable,','))) AND (@MailClass = '-1-' OR CON_MailClass = @MailClass) And (@MailShape = '-1-' OR CON_ProcessingCategory = @MailShape) And (@InductionDate = '-1-' OR CON_SCHEDULED_INDUCTION_DATE = @InductionDate) And (@VerificationLocation = '-1-' OR VerificationLocation like '%' + @VerificationLocation+ '%') And (@LocaleKey = '-1-&#

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

application role how query a view whose reference table is in another db?

Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

Ms sql query for this type of table structure

Hi This is my Table structure Field1       Filed2        001          A001          B001          C 001          D002          A003          B 003          C I need MS SQL query I need to show Field1 which has B and Not in DI mean 003 will shown coz it has B still D not comes... and 001 ,002 will not shown Coz 001 has B and D and 002 will not shown coz still it doesnt have B..Need Query... 

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Dynamically fill query parameters in a table adapter ?

SO I have created an object data source on a page from a table adapter using the various Wizards in Visual Studio.Its meant to query a diary Database by a DateTime parameter called diaryDate. I want to query from a date Now to the next 14 days ahead.I want to display the entries in a Grid View when the page loads.So I'm a bit stuck now as to how to set the two query parameters date1 and date2. I want to set date1 = now() and date2 = now() + 14 days.How do I set those parameters when the page loads ?Should I have used the adapter Wizard approach of do I have to write all the code in the code behind page ??This is my datasource code'<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GET_nextTwoWeeks" TypeName="query_getNextTwoWeeksTableAdapters.diaryDatesTableAdapter"> <SelectParameters> <asp:FormParameter DefaultValue="" FormField="date1" Name="date1" Type="DateTime" /> <asp:FormParameter DefaultValue="" FormField="date2" Name="date2" Type="DateTime" /> </SelectParameters> <InsertParameters>

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

dynamic table name linq-to-sql query


I am writing a function in which i generate autocode in specific format i want to make this function generalize so that i send tablename and columnname  parameter and function return the next code. I am using linq to sql with MVC e.g. i want to make table name and column name dyanamic

from m in db.<tblnameparam> 
                       orderby m.<colnameparam> descending
                       select m.<colnameparam>

SQL gets corrupted with 'WITH' command and rubbish when editing query containing derived table


When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable.  We have tried this with various existing and new, simple queries, all to no avail.  We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005.  I can edit the query in 2003 on my laptop with no ill effects.





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