Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
ADVERTISEMENT
Jul 9, 2007
Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
View 4 Replies
View Related
Feb 24, 2012
I have three tables.
Table USERS Contains columns User_id and UserName
Table DOMAIN Contains columns Domain_id and DomainName
Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
View 2 Replies
View Related
Jul 20, 2007
Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` (
`id_account` mediumint(8) unsigned NOT NULL auto_increment,
`userdata_id` mediumint(8) unsigned NOT NULL default '0',
`login` varchar(15) NOT NULL default '',
`pwd` varchar(15) NOT NULL default '',
`messenger_id` mediumint(8) unsigned NOT NULL default '0',
`logged` tinyint(1) NOT NULL default '0',
`ost_login` varchar(11) default NULL,
PRIMARY KEY (`id_account`),
UNIQUE KEY `messenger_UN` (`messenger_id`),
UNIQUE KEY `userdata_UN` (`userdata_id`)
)
INSERT INTO `account` VALUES (1, 1, 'User', 'fatimah', 4118394, 0, NULL);
INSERT INTO `account` VALUES (2, 2, 'Admin', 'haslo', 3333333, 0, NULL);
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it.
CREATE TABLE `contacts` (
`contact_id` mediumint(8) unsigned NOT NULL default '0',
`contacts` mediumint(8) unsigned NOT NULL default '0',
`auth` tinyint(1) unsigned NOT NULL default '0',
`ban` tinyint(1) unsigned NOT NULL default '0',
KEY `Contacts ID` (`contact_id`)
)
INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0);
INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum,
CREATE TABLE `messenger` (
`id_messenger` mediumint(8) unsigned NOT NULL default '0',
`status_id` tinyint(3) unsigned NOT NULL default '0',
`description` varchar(255) NOT NULL default '',
`archiwum` mediumtext NOT NULL,
PRIMARY KEY (`id_messenger`)
)
INSERT INTO `messenger` VALUES (1234567, 0, '', '');
INSERT INTO `messenger` VALUES (3333333, 1, '', '');
INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect).
CREATE TABLE `status` (
`id_status` tinyint(3) unsigned NOT NULL default '0',
`stat` varchar(15) default NULL,
PRIMARY KEY (`id_status`)
)
INSERT INTO `status` VALUES (0, 'offline');
INSERT INTO `status` VALUES (1, 'Online');
INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like:
id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this:
SELECT id_messenger,contacts,auth,ban,status_id
FROM account,messenger,contacts
WHERE account.login = 'User'
AND messenger.id_messenger = account.messenger_id
AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
thx in advance!
View 6 Replies
View Related
Nov 8, 2007
Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT *
FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P
WHERERB.MKT_SEG = @MKT_SEG
AND RB.CORP_ENT_CD = 'oh'
AND RB.RENEWAL_DT = '01/01/2008'
AND CRX.TRIGGER_TYPE_CD = 'P'
AND RB.BATCH_ID = CRX.BATCH_ID
AND CRX.CASE_ID = CX.CASE_ID
AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID
AND CRX.REN_PROP_NUM = P.PROP_NUM
AND P.PROP_STATUS <> 'C'
AND CX.ACCT_NBR = 123152
View 5 Replies
View Related
Dec 18, 2007
I have four tables. now i need to select the rows from all the four tables.
TABLE1: -Job
jobno
mtid
prid
mtpath
prpath
TABLE2: - livestaff this id will be store in Job
staffid
staffname
teamid
active
TABLE3: -masterstaff
mstaffid
staffname
teamid
active
TABLE4: -staffrel
masterstaffid
livestaffid
Now i need to select * from job and staffname from masterstaff and teamid from livestaff.
Please help
____________
Praba
View 2 Replies
View Related
Aug 8, 2007
Basically I have 5 tables. These are...
1/ RCPCrossRef
2/ RCPPositionData
3/ RGCrossRef
4/ RGData
5/ RComments
------------------------------------------------------
RCPCrossRef and RCPPositionData are related by these keys:
RCPPositionData.UniquePositionID = RCPCrossRef.CPPositionID
RGCrossRef and RGData are related by these keys:
ON RGData.PositionID = RGCrossRef.GPositionID
-----------------------------------------------------------
RCPCrossRef and RGCrossRef are related by these keys:
ON RCPCrossRef.GMatchID = RGCrossRef.GMatchID
But RCPCrossRef may also contain a NULL value for this key meaning no relationship exists for that row.
----------------------------------------------------------------
Finally RComments is related to RCPCrossRef and RGCrossRef by these keys...
ON RComments.GPositionID = RGCrossRef.GPositionID
ON RComments.CPPositionID = RCPCrossRef.CPPositionID
But again, one of these columns in the RComments table could contain a NULL value meaning no relationship exists for that row of data.
---------------------------------------------------------------
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments,
gc.GPositionID, cc.CPPositionID, cd.PositionDate
FROM ReconComments AS c
INNER JOIN
RGCrossRef AS gc
INNER JOIN
RGData AS gd
ON gc.GPositionID = gd.PositionID
ON c.GPositionID = gc.GPositionID
INNER JOIN
RCPData AS cd
INNER JOIN
RCPCrossRef AS cc
ON cd.UniquePositionID = cc.CPPositionID
ON c.CPPositionID = cc.CPPositionID
WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
Thanks.
View 6 Replies
View Related
Apr 7, 2008
Hi,
I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.
I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.
Here is a simplified version of my schema. Hourly charges are one type of charge:
charges table
=============
id int (autoincremented primary key)
date datetime
amount money
hourly_charges table
====================
charge_id int (primary key, also a foreign key to charges table)
start_time datetime
end_time datetime
I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.
Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:
INSERT INTO charges JOIN hourly_charges
(
charges.date,
charges.amount,
hourly_charges.charge_id,
hourly_charges.start_time,
hourly_charges.end_time
)
SELECT
date,
amount,
SCOPE_IDENTITY(),
start_time,
end_time
FROM charges
JOIN hourly_charges
ON charges.id = hourly_charges.charge_id
WHERE some condition is true
Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.
Thanks,
Adam Soltys
http://adamsoltys.com/
View 3 Replies
View Related
Oct 5, 2013
I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.
SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'
Table 1 HOLIDAY_REF
holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain
Table 2 BOOKING
holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600
Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1
Not sure I'm going about it the right way.
View 3 Replies
View Related
Sep 27, 2007
Hi!
I want to get some fields from more than one table. How can I use select command to do this? Please help me! The results should be in one table only!
Thanks in advance!
View 9 Replies
View Related
Feb 14, 2006
Hi,I have two tables: Code and Color.The create command for them is :create table Color(Partnum varchar(10),Eng_Color char(10),Span_Color char(20),Frch_Color char(20),CONSTRAINT pkPartnum PRIMARY KEY(Partnum))create table Code(Partnum varchar(10),Barcode varchar(11),I2of5s varchar(13),I2of5m varchar(13),UPC varchar(11),BigboxBCode varchar(11),DrumBCode varchar(11),TrayBCode varchar(11),QtyBCode varchar(11),CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum))Now my question is,how can i give a select statement such that I can get all the fields asoutput.Also plz note that the above is a sample. I have another 9 tables and Ineed a solutionsuch that on being refered by Partnum, I can get all the attributes.Thanks
View 19 Replies
View Related
Aug 20, 2007
Hi,
I have a number of related tables:
RGData is related to RGCrossReference
RCPPositionData is related to RCPCrossReference
RGCrossReference is also related to RCPCrossReference.
The data is returned correctly from these tables.
However, I also want to return data from another table - RComments.
How do I do this?
RComments is related to either RGData or RCPPositionData only.
Thanks.
Code Snippet
SELECT cm.CommentImage AS ViewComment, gd.PositionID AS GPositionID, cd.UniquePositionID AS CPPositionID
FROM RGData gd
INNER JOIN
RGCrossReference g
ON g.GPositionID = gd.PositionID
INNER JOIN
RCPCrossReference c
ON c.GMatchID = g.GMatchID
INNER JOIN
RCPPositionData cd
ON cd.UniquePositionID = c.CPPositionID
left outer JOIN
RComments cm
ON ((cm.CPPositionID = cd.UniquePositionID) or (cm.GPositionID = gd.PositionID))
AND cm.CommentsDate =
(SELECT MAX(CommentsDate) AS Expr1
FROM RComments
WHERE (GPositionID = g.GPositionID))
WHERE
(cd.Quantity != gd.Quantity
OR
cd.Currency != gd.Currency)
AND g.ForcedMatch = 'no';
View 3 Replies
View Related
Jul 13, 2015
I have the following two tables....
tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date
I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
View 11 Replies
View Related
Oct 17, 2005
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
View 2 Replies
View Related
Feb 18, 2004
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
Any ideas?
Thanks in advance.
View 6 Replies
View Related
May 10, 2014
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
View 3 Replies
View Related
Apr 10, 2007
Hi there,
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
View 2 Replies
View Related
Aug 25, 2006
Hello,
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
Thank you,
Nathan
View 17 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Nov 16, 2007
Hello,
I have a table which when i execute a select statement the execution lucks performance. The table currently has 1000 rows.
Table:
CREATE TABLE [dbo].[Listings](
[ListingID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[NodeID] [int] NOT NULL,
[Title] [varchar](100) NOT NULL,
[Description] [varchar](500) NOT NULL,
[Keywords] [varchar](200) NOT NULL,
[URL] [varchar](150) NOT NULL,
[DateSubmited] [datetime] NOT NULL
) ON [PRIMARY]
Even a simple sql statement without any where clause will take 3 seconds to execute.
select LISTINGID, TITLE, DESCRIPTION from listings
Is there anyway to improve that?
Thanks in advance.
View 2 Replies
View Related
Jan 24, 2008
I have a table of products which contains a parent-child hierarchy (a self-relationship, creating a built-in recursive structure using a foreign key). The point is: I must create a query that shows only products which are not parents. I can achieve this using a "not in" or a "full join".
Can anybody tell me which of the 2 below is the best option, and why?
Code Snippet
SELECT ProductID
FROM CRP.Product
WHERE ProductID NOT IN
(SELECT ProductFatherID FROM CRP.Product WHERE ProductFatherID is not NULL)
Execution plan:
Select (cost 0%) <--- Merge join (25%) <--- Clustered index scan (54%)
^--- Stream aggregate (1%) <--- Index seek (19%)
Code SnippetSELECT
PC.ProductID
FROM -- Product Child
CRP.Product PC
FULL JOIN -- Product Parent
(SELECT ProductFatherID ProductID
FROM CRP.Product
WHERE ProductFatherID is not NULL) PP ON (PC.ProductID = PP.ProductID)
WHERE
PP.ProductID is NULL
Execution plan:
Select (cost 0%) <--- Filter (2%) <--- Merge join (29%) <--- Clustered index scan (51%)
^--- Compute scalar (0%) <--- index seek (18%)
View 5 Replies
View Related
Jul 12, 2004
I have only been working with sql server for sever months so I would consider myself a novice.
I am trying to return 3 different datasets from the same query information.
for example a person will do a search for articles on "flowers red annuals"
I first want to return a datase with item details. Name of book, copyright, author etc...
I then want to populate 2 drop down lists to help narrow down the search
1. Article type
Newspaper(count)
Magazine(count)
Hardback Book(count)
Softback book(count)
Etc...
2. Publisher
Publisher A(count)
Publisher B(count)
Publisher C(count)
etc....
I am currently running 3 separate stored procedures against the master file to return this data.
1. select ... for the detail
2. Select ...count() group by for the other 2
Is there a better way to get this information without having to run the query 3 times?
Is there a way to run the (select..group by) against initial detail information to populate the 2 drop down controls.
Is there a way to return more that one dataset of information from one stroed procedure?
Because the data source has millions of records I would assume that this is inefficient or does it matter?
Thanks in advance for any advice.
View 1 Replies
View Related
Jan 18, 2001
I want to determine the performance impact caused by the extensive use of the 'select into #' statement in a production environment. The current situation is that our reports team extensively uses the 'select into #' statement to build smaller subsets of data. These subsets are then used as the basis to create summary style reports and exports. All this is accomplished via the use of SQL pass-through.
After these reports/exports are completed and tested, they are then released to our operations department and the users. The reports/exports then can be run against the production server at the discretion of the user, provided they have the appropriate permissions. These reports/exports target the live data on the primary production server that already has been designated for the use of the application software.
Now I know that reporting against a transactional-based server, where the users run the application, is not a very good idea. (Inherited) I am currently migrating all reports/exports to a reporting server. Although it will still be transaction-based, the reports/exports will be isolated from user activity. Eventually we will be moving toward a warehouse scenario.
I also know that the extensive use of the 'select into #' statement is not a coding practice for use in production. I provided several alternatives to this practice
1) insert..select 2) insert..execute - from stored procedure
I have read that in the in sql 6.5 that this may cause severe performance and locking behaviors in system db's and tempdb. However, in the following document on the Microsoft Knowledge Base, it indicates that SQL 7.0 may have corrected this issue.
Q153441 - FIX SELECT INTO Locking Behavior.htm
Despite the indication of being corrected, I am still not convinced. I am frequently seeing drastic performance hits, especially when several of the reports are running. (which is very common) My concern is that moving these reports/exports to a reporting server may save the users; I believe that it may be migrating the problem to another location. I will be working with the developers to optimize their code and will investigate index issues.
** To make a long story short. I would like someone who has experience with this provide me with the top 5+ reasons not to use the 'select into #' methodology in a production environment. Further, if anyone has any documentation, I would surely like the info.
Thanks, Dave
View 2 Replies
View Related
Mar 23, 2006
We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code
SELECT DISTINCT email FROM Subscribers
to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).
I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.
What do you performance gurus think?
View 3 Replies
View Related
Oct 17, 2007
I am running SQL 2000 SP4. I have a stored procedure which calls a function in an update statement. Previously, this SP which is pretty intensive and used to build a report would run in appox 10 minutes. Now it is taking 3 hours to execute. I have tracked the performance issue to the function. The function is taking 3 to 4 seconds to execute for each row and it is updating 2,600 rows.
I am including the select statement causing problems.
SELECT TOP 1
@m_ECodeMsg = tev.BillMsg
FROM tblUsageHistoryDetail tuhd
INNER JOIN tblMeterReadEcode tmre
ON tmre.MeterReadID = tuhd.CurrentMeterREadID
INNER JOIN tblUsageHistory tuh
ON tuh.UsageHistoryID = tuhd.UsageHistoryID
INNER JOIN tblTran tt
ON tt.TranID = tuh.TranID AND tt.ServiceLocationID = tuh.ServiceLocationID
INNER JOIN tblServiceLocation tsl
ON tsl.ServiceLocationID = tt.ServiceLocationID
INNER JOIN tblRateUsage tru
ON tru.RateID = tsl.RateID
INNER JOIN tblECodeValue tev
ON tev.ECodeValueID = tmre.ECodeValueID AND tev.ECodeTypeID = tmre.ECodeTypeID
WHERE tt.StatementID = @i_StatementID AND tru.AltServiceID = tsl.ServiceID
AND tt.TranTypeID = 2 AND tev.ECodeTypeID = 6
The function has been in use for over a year and SP4 has been installed on this server for serveral months now. If I run this on an old box with SP3a it runs fine. I have rewrote the statement without the "TOP 1" and execution of the function is less than 1 sec.
Any ideas as to why my performance has taken such a hit?
View 4 Replies
View Related
Jul 11, 2007
Hello -- thank you for taking the time to read this.
I have a very large table that is used both for archives and new information. To get the current information, the table is queried by many different users at various polling periods. The SELECT required includes about fifteen JOINS, and only returns about 200 rows at any given time.
So I got to thinking if it might be faster to periodically run the big query as a SELECT INTO into a smaller table and letting the polling clients query the smaller table with SELECT *. Periodically, the smaller table would be DROPPED and refereshed with another SELECT INTO.
Trouble is, the data would have to be updated once every 30 seconds, and there are inbound polls coming at the rate of about 200 per minute. It got me to thinking what might happen if a client attemtped to query the smaller table when it was in the process of being dropped and refilled.
So my question is three-part:
1) assuming a larger table of about 500,000 records and only 500 pertinent at any given time, is there any real potential of performance enhancement by switching to a SELECT INTO table?
2) if so, is there a chance of a client failing a query if the inbound query somehow collides with the DROP/SELECT INTO procedure?
3) if so, is there any way to prevent it or a better way of doing this?
Thanks again for reading, and in advance for any help you can provide. I apologize if I sound like a dummy - it's hard to fake intelligence!
View 3 Replies
View Related
Jul 20, 2005
Hi All,I am getting slower performance of select statements in MS SQL. I amfinding select statements in MS SQL are even slower than MS ACCESS. Isthere any way to improve the performance of select statements in MSSQL by tuning the database of anything else??Thanks in advance!Hoque
View 3 Replies
View Related
Apr 29, 2008
I have a table that contains 10 millions records. The following 2 statements, which one provide better performance? Frankly, i have no idea how to compare the execution plan...
Select Top 10000 * From Table
OR
Set rowcount 10000
Select * From Table
View 11 Replies
View Related
Jul 23, 2005
I've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
View 1 Replies
View Related
Nov 28, 2007
Hi.
We are now working with SQL2000sp4, planning migration to SQL2005 in few months though.
I've faced performance issues with large tables (200-500 mln rows, 50-100Gb of data+indexes)
New data are uploaded into tables once a day, around 1mln rows. Thats the only time of inserting data, during daytime tables are used for SELECTs only.
The problem that daily INSERTs are taking too much time now, because of rebuilding few indexes for the table.
I noticed that partitioning solution looks like solving this problem well. So i splitted master data table into 4 tables:
old master table:
CREATE TABLE [dbo].[DTB] (
[report_date] [smalldatetime] NOT NULL ,
[param] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[param_value1] [decimal](18, 2) NULL ,
[param_value2] [decimal](18, 2) NULL ,
[param_value3] [smallint] NULL
)
CREATE INDEX [IX_DTB_DT_ACC] ON [dbo].[DTB]([report_date], [param])
new partition1:
CREATE TABLE [dbo].[be_data_DTB_part_2007_q1] (
[report_date] [smalldatetime] NOT NULL ,
[param] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[param_value1] [decimal](18, 2) NULL ,
[param_value2] [decimal](18, 2) NULL ,
[param_value3] [smallint] NULL ,
CONSTRAINT [CK_be_data_DTB_part_2007_q1_report_date] CHECK ([report_date] >= '2007-Jan-01' and [report_date] <= '2007-Mar-31')
)
CREATE CLUSTERED INDEX [idc_be_data_DTB_part_2007_q1_report_date_param] ON [dbo].[be_data_DTB_part_2007_q1]([report_date], [param])
Similar are definitons for other partitions - q2, q3 and q4.
And here is partitioned view itself:
create view dbo.data_DTB
as
select * from dbo.be_data_DTB_part_2007_q1
union all
select * from dbo.be_data_DTB_part_2007_q2
union all
select * from dbo.be_data_DTB_part_2007_q3
union all
select * from dbo.be_data_DTB_part_2007_q4
I want users to access data SELECTing from view data_DTB, while I perform daily inserts right into be_data_DTB_part_2007_q4.
In general, this solution works well. For example:
Code Block
set statistics profile on
go
select * from data_DTB where report_date = '2007-Apr-16'
go
set statistics profile off
go
1290674 1 SELECT * FROM [data_DTB] WHERE [report_date]=@1
1290674 1 |--Concatenation
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Mar 31 2007 12:00AM' AND Convert([@1])>='Jan 1 2007 12:00AM')))
0 0 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q1].[idc_be_data_DTB_part_2007_q1_report_date_param]), SEEK:([be_data_DTB_part_2007_q1].[report_date]=Convert([@1])) ORDERED FORWARD)
1290674 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Jun 30 2007 12:00AM' AND Convert([@1])>='Apr 1 2007 12:00AM')))
1290674 1 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q2].[idc_be_data_DTB_part_2007_q2_report_date_param]), SEEK:([be_data_DTB_part_2007_q2].[report_date]=Convert([@1])) ORDERED FORWARD)
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Sep 30 2007 12:00AM' AND Convert([@1])>='Jul 1 2007 12:00AM')))
0 0 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q3].[idc_be_data_DTB_part_2007_q3_report_date_param]), SEEK:([be_data_DTB_part_2007_q3].[report_date]=Convert([@1])) ORDERED FORWARD)
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2007 12:00AM' AND Convert([@1])>='Oct 1 2007 12:00AM')))
0 0 |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q4].[idc_be_data_DTB_part_2007_q4_report_date_param]), SEEK:([be_data_DTB_part_2007_q4].[report_date]=Convert([@1])) ORDERED FORWARD)
As far as i see it checks filter parameter fitting CHECK constraint for each partition. Then it peforms clustered index seek for partition actually containing data and avoids using 3 other partitions. Thats great! This example just illustraits that partitioning actually works for me.
Unfortunately, there is another query with just awful performance on partitions comparing to single table. Lets try to get few rows entered last day:
Code Block
set showplan_text on
go
select top 10 * from DTB order by report_date desc
go
set showplan_text off
go
|--Top(10)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([MYDB].[dbo].[DTB]))
|--Index Scan(OBJECT:([MYDB].[dbo].[DTB].[IX_DTB_DT_ACC]), ORDERED BACKWARD)
Excellent! It runs just for only few seconds. But using partitions:
Code Block
set showplan_text on
go
select top 10 * from data_DTB order by report_date
go
set showplan_text off
go
|--Top(10)
|--Merge Join(Concatenation)
|--Merge Join(Concatenation)
| |--Merge Join(Concatenation)
| | |--Sort(ORDER BY:([be_data_ DTB_part_2007_q1].[report_date] ASC, [be_data_ DTB_part_2007_q1].[param] ASC, [be_data_DTB_part_2007_q1].[param_value1] ASC, [be_data_ DTB_part_2007_q1].[param_value2] ASC, [be_
| | | |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q1].[idc_be_data_DTB_part_2007_q1_report_date_param]))
| | |--Sort(ORDER BY:([be_data_DTB_part_2007_q2].[report_date] ASC, [be_data_ DTB_part_2007_q2].[param] ASC, [be_data_DTB_part_2007_q2].[prama_value1] ASC, [be_data_DTB_part_2007_q2].[param_value2] ASC, [be_
| | |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q2].[idc_be_data_DTB_part_2007_q2_report_date_param]))
| |--Sort(ORDER BY:([be_data_DTB_part_2007_q3].[report_date] ASC, [be_data_DTB_part_2007_q3].[param] ASC, [be_data_DTB_part_2007_q3].[param_value1] ASC, [be_data_DTB_part_2007_q3].[param_value2] ASC, [be_data_
| |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q3].[idc_be_data_DTB_part_2007_q3_report_date_param]))
|--Sort(ORDER BY:([be_data_DTB_part_2007_q4].[report_date] ASC, [be_data_DTB_part_2007_q4].[param] ASC, [be_data_DTB_part_2007_q4].[param_value1] ASC, [be_data_DTB_part_2007_q4].[param_value2] ASC, [be_data_
|--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q4].[idc_be_data_DTB_part_2007_q4_report_date_param]))
As one can see that€™s just awful . When I make graphical execution plan with Ctrl+L it says costs for Sort operations are thousands. I didn€™t run this query to check statistics profile, because on our server it will run for hours.
I found a topic regarding this problem: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1270479&SiteID=17
Mostafa Elhemali describes exactly my problem in the last post. I also though that getting top 10 * from partitioned view shouldn€™t be a problem €“ it€™s quite obvious just to grab top 10 from each partition and then find top 10 amongst them. Looks like it doesn€™t work this way though.
So the question is. Is there any new workarounds for this problem? Or maybe it is already solved in latest patches for SQL2005? I know that SQL2005 introduces new way of partitioning tables, maybe the problem will go if using SQL2005 partitioned tables instead of oldstyle partitioned views?
Thank you.
p.s. Upon reviewing my post i noticed that issued ORDER BY report_date DESC against unpartitioned table, and ORDER BY report_date against partitioned view. Well, specifying ORDER BY report_date DESC for partitioned view gives similar results, except for few ASCs are replaced with DESCs.
View 2 Replies
View Related
Jun 15, 2015
the cursor at the bottom  iterates only to print the number of rows.The problem is in the select. This takes 30 seconds to iterate through 1242 records.But if I add a TOP 1000000 or whatever number to the select, the same iteration takes less than a 1 second.I've tested each query without cursor, and  both have the same cost and performance. (Not exactly the same plan)Note that I got the same performance improvement declaring the cursor as STATIC.Why the top is affecting the cursor iteration so much?
Declare @query varchar(512)
DECLARE Itera CURSOR --LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select --TOP 1000000
[code]....
View 2 Replies
View Related
Oct 25, 2006
SQL 2000 Connection String:user id=MyUserName;password=MyPassword;initial catalog=MyDB;server=MyServer;Connect Timeout=30 This SELECT statement returns its 10 results nearly instantly:SELECT * FROM MyTableDitto from above, but completes in 30-40 seconds:SELECT * FROM [dbo].[MyTable]Ditto from above, but completes nearly instantly:
SELECT TOP 1000 * FROM [dbo].[MyTable] Obviously I have stopped using the [dbo] syntax in my SqlCommand's (SELECT's and EXECUTE's) but still would like to know why this is.vr, Rich
View 3 Replies
View Related
