SQL query with multiple columns

I am trying to get a query to obtain results from a table that
contains approx. 12 million records in short time frame.

Below is an example of the query.
select sum(field_1) as total, field_2, field_3, field_4, field_5,
field_6,
field_7, field_8, field_9, field_10, field_11, field_12, field_13
from table_a
where datepart(M, field_4) in
( '1','2','3','4','5','6','7','8','9','10','11','12')
and datepart(YYYY, field_4) in ('2004','2005','2006','2007,'2008')
group by field_2, field_3, field_4, field_5, field_6, field_7,
field_8, field_9, field_10, field_11, field_12, field_13
order by field_2, field_3

The field_1, field_2, field_3, field_5, field_6, field_7, field_8,
field_9, field_10, field_11, field_12, field_13 columns are used in a
report to total up the final numbers.

I put an clustered index on field_2 and field_3.

The query takes aprox over a minute.

Is there anyting else that can be done?

Thanks in advance.



0
user
2/14/2010 2:03:18 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
579 Views

Similar Articles

[PageSpeed] 43

I would certainly rewrite the where condition for this query as

select sum(field_1) as total,
 field_2, field_3, field_4,
 field_5, field_6, field_7,
 field_8, field_9, field_10,
 field_11, field_12, field_13
from table_a
where field_4 >= '20040101' And field_4 < '20090101'
group by field_2, field_3, field_4,
 field_5, field_6, field_7,
 field_8, field_9, field_10,
 field_11, field_12, field_13
order by field_2, field_3

Whether or not that would make a significant difference in your performance 
depends on many factors.  If however, a significant protion of your table 
has values for field_4 either before 2004 or after 2008, and you changed the 
clustered index to be on field_4, then there would be a significant 
improvement in the performance of this query if you rewrote it in the manner 
shown above.  Of course, changing the clustered index might make other 
queries worse, so you might have to test to see if the overall performance 
was better.

Tom

"user" <kssngrm@ix.netcom.com> wrote in message 
news:f7c4ca41-1bbb-40fa-a80b-70269ea959a0@b9g2000pri.googlegroups.com...
>I am trying to get a query to obtain results from a table that
> contains approx. 12 million records in short time frame.
>
> Below is an example of the query.
> select sum(field_1) as total, field_2, field_3, field_4, field_5,
> field_6,
> field_7, field_8, field_9, field_10, field_11, field_12, field_13
> from table_a
> where datepart(M, field_4) in
> ( '1','2','3','4','5','6','7','8','9','10','11','12')
> and datepart(YYYY, field_4) in ('2004','2005','2006','2007,'2008')
> group by field_2, field_3, field_4, field_5, field_6, field_7,
> field_8, field_9, field_10, field_11, field_12, field_13
> order by field_2, field_3
>
> The field_1, field_2, field_3, field_5, field_6, field_7, field_8,
> field_9, field_10, field_11, field_12, field_13 columns are used in a
> report to total up the final numbers.
>
> I put an clustered index on field_2 and field_3.
>
> The query takes aprox over a minute.
>
> Is there anyting else that can be done?
>
> Thanks in advance.
>
>
> 

0
Tom
2/14/2010 2:29:42 AM
Hello (do you have a name?)

Can we take a hard-nosed attitude to this?
Why is a minute too long? It is processing 12 million rows...

My guess is that this query is not run many times a day so is a minute 
really worth worrying about. Especially when I suspect the report is or 
should be scheduled. You also did not explain how many resulting rows 
are involved.

Geoff Schaller
Software Objectives


"user" <kssngrm@ix.netcom.com> wrote in message 
news:f7c4ca41-1bbb-40fa-a80b-70269ea959a0@b9g2000pri.googlegroups.com:

> I am trying to get a query to obtain results from a table that
> contains approx. 12 million records in short time frame.
>
> Below is an example of the query.
> select sum(field_1) as total, field_2, field_3, field_4, field_5,
> field_6,
> field_7, field_8, field_9, field_10, field_11, field_12, field_13
> from table_a
> where datepart(M, field_4) in
> ( '1','2','3','4','5','6','7','8','9','10','11','12')
> and datepart(YYYY, field_4) in ('2004','2005','2006','2007,'2008')
> group by field_2, field_3, field_4, field_5, field_6, field_7,
> field_8, field_9, field_10, field_11, field_12, field_13
> order by field_2, field_3
>
> The field_1, field_2, field_3, field_5, field_6, field_7, field_8,
> field_9, field_10, field_11, field_12, field_13 columns are used in a
> report to total up the final numbers.
>
> I put an clustered index on field_2 and field_3.
>
> The query takes aprox over a minute.
>
> Is there anyting else that can be done?
>
> Thanks in advance.

0
Geoff
2/14/2010 3:52:55 AM
user wrote:
> I am trying to get a query to obtain results from a table that
> contains approx. 12 million records in short time frame.
>
> Below is an example of the query.
>...
> where datepart(M, field_4) in
> ( '1','2','3','4','5','6','7','8','9','10','11','12')
>...

Datepart returns an integer, you might as well treat it as an integer rather 
than having to convert it to a string every time.

Or maybe SQL Server converts the in list to integers first, but you don't 
need to give it more work if you're trying to make it faster.

Andrew 


0
Andrew
2/15/2010 12:47:18 PM
Reply:

Similar Artilces:

Copying data to a blank column
I would like to copy data from 1 column in sheet1 starting at row 12 to the first empty column starting at row 8 in sheet2. sub trythis()'SAS untested sc=2 slr=sheets("sheet1").cells(rows.count,sc).end(xlup).row with sheets("sheet2") dlc=.cells(1,columns.count).end(xltoleft).column+1 sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc) end with end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in message news:6F9BC9EC-E48E-42CC-84D8-01C007B0C6E8@micr...

union query?
I have four tables that I need to query to use as the control source for a combo box. The combo box is cbLocations and the four tables and fields are: [tblCustomerDCs].[txtCustomerDCID].[txtCustomerName] [tblCustomerIDs].[txtCustomerID].[txtCustomerName] [tblFacilities].[txtFacilityID].[FacilityName] [tblSupplierIDs].[txtSupplierID].[txtSupplierName] I need to store the txtID values. All of the tables have a different number of records and may also have similar IDs. How in the world can I manage this? A union query seems the answer but I don't want to waste a bunch of time with that...

Query for existence of table
Hi all,What sort of SQL query could be used in an Access database to determine the existence of a table? I can do this in SQL server by looking at sysobjects - but how does one do it in Access? I have an application that needs to check to see if a table exists and, if not, create it on-the-fly.Thanks,-bruce create procedure sphappyasif exists (select name from sysobjects where id =object_id('mytable'))begindrop table mytableendOn Mar 20, 4:51 pm, "Microsoft Newsgroups" <broe...@cfl.rr.com> wrote:> Hi all,>> What sort of SQL query could be used in an Access da...

very interesting query- please help
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Need help building query
I'm not that familiar to Access and would appreciate some help building the following SQL query. I have two tables, and if two categories match within the tables, then I would like a third category entry updated to the first table. 1st Table: Working 2nd Table: Original Categories (columns) that should match between table 1 and 2: Pt Acct # Charge Cd If the entires for a line match, then update: Lawson # (take from Original table and enter into line for Working table) So, basically if the (Pt Acct #) AND (Charge Cd) in the (Original) table match the (Pt Acct #) AND (Charge Cd) in...

UPDATE QUERY not updating data
Hi all, Is there something obvious I should know about or is it plain magic? I've got a Access 2000 file format database in Access 2003. I've got a table called [Jobs] and I'm developing a way to edit a Job. So I've created an identical table called [EditedJob]. After clicking EDIT I clear the table [EditedJob], then populate it with all the details of a current job and open a form frmEditJob which is bound to [EditedJob] table. Now, I am able to change all the details I want and when clicking "save" button I run this code: Dim qryUpdate As QueryDef Set qryUpdat...

Database Query #3
I posted this earlier with no replies. Anyone got any ideas? I am using a database query to retrieve data from another database excel file. The problem I am having is that the column of data which I have contains records which have both text and number formats. When the data transfers, it only brings those records which are number format. Is there any way to make is so that the database query will pull both number and text formats, as I can't easily change it so that it is one or the other? Thanks The following article in Dick Kusleika's web log may help: http://www.di...

Multiple email accounts #15
I am tryiing to find out how many email accounts can you setup in Outlook2007. From what i see you can only have 2. Any more than that won't show. -- Mrnbooc "mrnbooc" <mrnbooc@discussions.microsoft.com> wrote in message news:5D534405-53EF-4231-9FE5-5B13BE4EED94@microsoft.com... >I am tryiing to find out how many email accounts can you setup in > Outlook2007. From what i see you can only have 2. Any more than that > won't > show. > -- > Mrnbooc > > If they are all pop accounts then NONE of them will "show". I'm presuming...

Form Error (Query Update)
Hi, I have about 12 people to work on the database and each has own userID and password. Their UserID and Password input and the form link to the query that has the filter on "UserID and Password" for their input. The message said "Can't Update; Query Locked". Is there I do something wrong? I thought the form is the shared for everyone to input based by UserID and Password. Please help. Thanks We aren't there. We can't see how your database is set up. For instance, do you have a split database, with a single "back-end" file on your network an...

group query 02-05-10
I'm trying to create a query that gives me the average price of each product based on the last 10 purchases. The table is called "t_worksheet", with fields called "buy_price_alt_currency" which represents the price, "processing_grade" which represents the product and "collection_date" which represents the purchase date. I've been struggling with sub queries but can't get the results I need. Please could somebody help. Thanks Ian I think this will work (UNTESTED) -- qryLastTen -- SELECT processing_grade, buy...

HELP!!! How do you create a query crieria for multi values??
Hello all, please hyelp, its an emergency!!! Need to create a query where one of the searches is male or female, how do I allow user to enter both as the search options?? Also how do I convert tix boxes into a query criteria (e.g. serach for all ticked) Thanks so much for your help ...

Multiple IIf statements
I'm having trouble placing multiple Include If (IIf) statements in a query. There are a number of organisms with full names in our database, I want to pull them but have them all displayed under the most generic of their names. The following statement works fine up until the second IIf. When I add that segment, I get a message saying I there is a syntax error or I have not included enough brackets. Org_1 Change: IIf([Org_1] Like "ESBL*","ESBL",[Org_1]),IIf[Org_1] Like "CR*","Carbapenem",[Org_1]) The mistake is probably obvious, but could so...

Linking shapes to SQL database
Please can someone help me - I'm tearing my hair out! I am creating Visio drawings of our server racks using Visio 2003 SP2 and HP shapes/stencils from www.visiocafe.com I am trying to link the shapes in the drawing to a SQL database and then publish as a web page. I have run the database wizard and selected the correct record in the database and in the Visio drawing everything looks and works fine. If I choose properties on the shape I get the correct data from the database. However, if I then try to save as a web page and publish the files, it all goes screwy. Some of the shapes are fi...

XMLSerializer and SQL Server
Hello, I'm trying to use the FOR XML EXPLICIT clause with SQL Server to deserialize data from my database into a strongly-typed collection object that I will use throughout my application. I initially tested my design by building a collection in code and then serializing it to/from an XML file, which worked fine. However, I have hit a brick wall trying to restore the data from SQL Server. I originally had my collection and object classes just marked as [Serializable()] without any of the XML Serialization attributes; however, replacing the "standard" serialization a...

SQL Smartlist uses?
Hi, I am not a GP user but I need to use SQL to retrieve information from it. The information is in a smart list my client created. I used the smartlist builder to find each table & view used to create the data set. I tried to mirror the joins the smartlist builder uses but I don't get the entire data set. The smartlist returns data for 2007 but my query returns data up to 12/31/2006. How can I get the same results the smartlist yields from SQL? Thanks, Ivan Ivan, If this query is against the GL, from what you're saying, it sounds like 2006 has been closed, and 2007 is open...

multiple page banner
Does MS Publisher allow to make banners that are 'double' wide. The ONLY thing I can see is the pages can ONLY be put serially. That is wether in portrait or landscape, the pages in the banner join to extend the length. I want to do a 6 page banner that is only 3 pages long but double rowed (2 pages wide). BQ Super_BQ <Super_BQ@discussions.microsoft.com> was very recently heard to utter: > I want to do a 6 page banner that is only 3 pages long but double > rowed (2 pages wide). You will want to use a custom page size - set the height to the height of two pages min...

Standby CRM 4 / SQL 2005 server
Hey all, We are trying to implement a redundancy for both the CRM Server and the SQL Server. In our case, we have CRM 4.0 Server and SQL 2005 Server on the same physical machine, called SVR1. We have a second machine that will be a backup for CRM and SQL, this machine is called SVR2. As per the installation guide, when setting up SVR2, we installed SQL Server 2005, followed by CRM Server. During the CRM server install, we selected "Use an existing installation," and thus no SQL DBs were created on SVR2. Lastly, we set up a mirror of the MSCRM_CONFIG and COMPANY_MSCRM databases, wit...

Reconcile multiple accounts
I am currently using Money 2004 and have three checking accouts set up in Money but all three are actually one bank account. In an older version of MS Money I could combine multiple account to reconcile as one. Does anyone know if I can do this is Money 2004? If not, I am ready and willing to upgrade to Money Plus Deluxe but want to make sure the combined balancing feature is available before I purchase. Does anyone know if I can do this in Plus Deluxe? Thanks! Really? I don't think this--subaccounts--has EVER been supported by Money. I could be wrong. "jen361" <...

using multiple conditions
I would like to know how to write the syntax to test for 1. age>44 or 2. Seniority is >9 and by determining the answer to that 3. Arsenic exposure >4.9 4. Cadmium exposure >2.4 If 1. is true or 2. true then I need to check 3. and 4. if 1. is true or 2. is true and 3 and 4 are false then ? if 1 or 2 is false and 3 or 4 is false then something else would I use and or /and condition. The way I wrote my syntax the or works but the and does not. Any clues on how to write the syntax to check for all 4 conditions? This is one solution: ="1 is "&...

saving multiple emails with attachments in Outlook 2003
Hi, I want to be able to save multiple email messages along with their attachments. Into a folder on my computer. I know that I can select the email, and then go to the File -> Save As.. and save the email with its attachment as type outlook message (.msg) to do the job for a single email. Is there a way to do this quickly for many emails all at once? When you're dealing with 100+ emails doing it that way just takes way too long. Thanks! The easiest way to save multiple email messages is to create a new Personal Folder (called a .pst file) and move the messages into that f...

SUMIF and multiple criteria
I have a spread sheet with approx 3000 rows of info The columns A and D of information have thee the criteria I would like to use. Column A is a 4-character alpha-numeric field Column D is a Alpha field For each instance of the value in column A which meets the criteria in colum D, I need to SUM column C. Can anyone help? Please show sample of what you need -- Greatly appreciated Eva "UCfts" wrote: > I have a spread sheet with approx 3000 rows of info > > The columns A and D of information have thee the criteria I would like to use. > C...

What is the formula to convert a whole column containing date to text?
What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

Display Date from Unbound Field in Form in a Query
Just had an issue that in 10 years of using Access never saw - just wondering if I just never encountered it or what... I have a form running a query, the user enters a date. The report needs to show the date - so the query has a field like this: report date: [Forms!]![frm_report]![txtReportDate] I had a problem exporting to Excel - seems the direct placement in the query led to characters that Excel could not understand. I actually had another date field from the form that I did a date add on, it worked fine. I eventaully put the form reference in a Format, the issue was gone. The...

Excel Query Opens Source
All, I am one of several offices supported by a purchasing group. This purchasing group maintains the status of all purchases in an Excel workbook. They will not open up the workbook for all. I have convinced them to let me create a workbook that queries theirs and returns just my offices purchases. They will have given everyone access required so that the query will work. As long as the exact location is not readily available and all of the data is not available, they are happy. Everything worked great for two weeks, but a week ago when the data is refreshed, the entire source workbook is o...

decreasing performance when SQL in mixed mode
The installation guide of MS says that you should use as authentication method Windows Only or Mixed Mode for SQL server. Only Windows only would be safer. Because of different reasons (a mailmerge Add_on and the server is hosted at an ASP), we would like to set the authentication mode of the SQL server to Mixed Mode. The performance of the CRM server is going down dramatically when you do that. Anyone any idea how to solve this problem? We have MS Crm 1.2, Small Business Server 2003, A dual Xeon with 2 Megabytes of Ram and I think 100 accounts in the database. ...