I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Hi, when running the following stored procedure: ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]@LeidinggevendeID int,@Start int = 1,@Limit int = 25,@Sofinummer int = NULL,@Achternaam nvarchar(128) = NULL,@Functie nvarchar(64) = NULL ASWITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY Achternaam) AS RowNumber,Persoon.*FROM PersoonINNER JOIN DienstverbandON Persoon.ID = Dienstverband.PersoonIDINNER JOIN BedrijfsonderdeelON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelIDINNER JOIN LeidinggevendeON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelIDWHERE Leidinggevende.Begindatum <= getdate()AND (Leidinggevende.Einddatum > getdate()OR Leidinggevende.Einddatum IS NULL ) AND Leidinggevende.PersoonID = @LeidinggevendeIDAND ( Sofinummer = @Sofinummer OR @Sofinummer IS NULL ) AND ( Achternaam LIKE @AchternaamOR AchternaamPartner LIKE @AchternaamOR @Achternaam IS NULL ) ) SELECT *FROM OrderedWHERE RowNumber between @Start and (@Start + @Limit - 1) When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code: [DataObject(true)] public class PersoonFactory { [DataObjectMethod(DataObjectMethodType.Select, false)] public static IList WerknemersBijLeidinggevende(int ldgID, int start, int max) { IList list = new List(); SqlDataReader rdr = null; SqlConnection connection = DatabaseProvider.Connection; SqlCommand command = new SqlCommand("GetWerknemersBijLeidinggevende", connection); command.Parameters.AddWithValue("LeidinggevendeID", ldgID); command.CommandType = CommandType.StoredProcedure; try { connection.Open(); rdr = command.ExecuteReader(CommandBehavior.CloseConnection); while (rdr.Read()) { Persoon pers = new Persoon(); pers.ID = rdr["ID"] as int?; pers.Achternaam = rdr["Achternaam"] as string; pers.AchternaamPartner = rdr["AchternaamPartner"] as string; pers.Achtertitels = rdr["Achtertitels"] as string; pers.DatumOverlijden = rdr["DatumOverlijden"] as DateTime?; pers.Geboortedatum = rdr["Geboortedatum"] as DateTime?; pers.Geslacht = rdr["Geslacht"] as string; pers.Middentitels = rdr["Middentitels"] as string; pers.Naamgebruik = (int)rdr["Naamgebruik"]; pers.Sofinummer = rdr["Sofinummer"] as string; pers.Voorletters = rdr["Voorletters"] as string; pers.Voortitels = rdr["Voortitels"] as string; pers.Voorvoegsel = rdr["Voorvoegsel"] as string; pers.VoorvoegselPartner = rdr["VoorvoegselPartner"] as string; list.Add(pers); } } catch { throw; } finally { if (rdr != null) rdr.Close(); else connection.Close(); } return list; } I get 0 rows all of a sudden. Any idea why?
I have an ASP.NET application where I need to filter the records returned from a SQL query using a calculated distance to a point that is set by the user. To do this directly, I would need to include a square root of an expression in the SQL query. It seems like I should be able to do this by writing my SQL query something like: SELECT SQRT(expression ....) AS distance, column2, column3 ... FROM mytable WHERE distance < 50 Unfortunately, SQL queries apparently don't like the SQRT function. I am using .NET 2.0 with VS2005 and a MS Access backend (that will soon be moved to SQL/Server). As an alternate, maybe I can query the data into a temporary record set with a extra field for distance and then step my way through the temporary record set and replace the values in the distance field with a calculated distance. Once the temporary record set is prepared, then I would delete the records that don't pass the distance requirement or otherwise hide them and bind the data to a gridview control. Thanks, David
is there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
I have a View which does what I need it too-select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatusFrom department dLeft join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)Left join (Select Count(*) cnt, deptid from employeeeval join employee on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)Brings back data as follows:Cast - Cast - 00999 - 09246 - reviews still in progressCMP- Copper Metal Products - 03315 - 09246 - reviews are completeCNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progressCRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progressSo I did the following: select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName, CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatusfrom vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)ORDER BY e.DeptID, e.LastNameNow this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following: Cast - Atkins, Carl - 09935 - Chris Burke - Not StartedCast - Bridges, James - 09929 - Chris Burke - In Progress CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not StartedCNS - Mitchell, Bill - 06200 - Kahle Rummer - In ProgressNow what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progressCast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progressCMP - NULL - NULL - Fred Grove - NULL - reviews are completeCNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progressCNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progressCRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progressCRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progressMicroMll - NULL - NULL - Tim Cross - NULL - reviews are completeI hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this: Dept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept Dept ID- Supervisor- Reviews Complete No data shown since COMPELTEDept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS Select * from don0001 left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
Hi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.
Hi All, I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join? Table A Table B Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5 A Jan 5 xxx A Jan 1 yyy B Feb 3 C Mar 4 B Mar 4 C Apr 3 C Apr 2 D May 2 E Mar 1
Result Table Column1 Column2 Column3 Column4 Column 5 A Jan 6 (= 5+1) xxx yyyy B Feb 3 B Mar 4 C Mar 4 C Apr 5 (= 2+3) D May 2 E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
I'm have made a series of tables and views to create some reports. I have included boiled-down versions of them below. The report query that I want to run (at the bottom) takes a long time to execute and sometimes times out. Creating a rank within the query is doing the most damage, but it can still take a long time to execute without it. The usage table has almost 70k records and there are over 10k users. Does anyone have suggestions for making these views/queries more efficient?
CREATE VIEW VUsageSubtotals AS SELECT TOP 100 PERCENT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, usage.accesstype, SUM(usage.cost) AS subtotal, MONTH(usage.gmttime) AS [month], VUsageTotals.total FROM VUserGroupDates INNER JOIN people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN usage ON people.strUserID = usage.strUserID INNER JOIN VUsageTotals ON VUserGroupDates.strUserID = VUsageTotals.strUserID AND VUserGroupDates.intGroupID = VUsageTotals.intGroupID AND MONTH(usage.gmttime) = VUsageTotals.[month] WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate) GROUP BY VUserGroupDates.strUserID, VUserGroupDates.intGroupID, usage.accesstype, MONTH(usage.gmttime), VUsageTotals.total
==========================
CREATE VIEW VUsageTotals AS SELECT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, SUM(usage.seconds) AS totaltime, SUM(usage.cost) AS totalcost, MONTH(usage.gmttime) AS [month] FROM VUserGroupDates INNER JOIN people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN usage ON people.strUserID = usage.userid WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate) GROUP BY VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, MONTH(usage.gmttime)
==========================
CREATE VIEW VUserGroupDates AS SELECT strUserID, intNewGroupID AS intGroupID, dtJoinDate, ISNULL ((SELECT MAX(TGA2.dtJoinDate) FROM TGroupAudit TGA2 WHERE TGA2.dtJoinDate < TGA1.dtJoinDate AND TGA2.strUserID = TGA1.strUserID), GETDATE()) AS dtLeaveDate FROM TGroupAudit TGA1
==========================
Here's a sample query that gets run for a report:
SELECT TOP 30 PERCENT WITH TIES V1.*, (SELECT COUNT(DISTINCT V2.subtotal) FROM VUsageSubtotals V2 WHERE V1.subtotal <= V2.subtotal AND V1.intGroupID = V2.intGroupID AND V2.month = 3) AS rank FROM VUsageSubtotals V1 WHERE strUserID IN ('a1, a2, b5, b7, b9, d12') AND V1.month = 3 AND accesstype = 'STANDARD' GROUP BY strUserID, intGroupID, subtotal, month, total, accesstype
The purpose of the report is an accounting one. It is desired to know how much each user used each accesstype during a month. Usage is further split up by the user's groups (which may never change or could several times during a month) for billing purposes.
I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.
The tables are basically set up like this:
TABLE 1
PrimaryKey
Textfield1
Textfield2
Textfield3
TABLE 2
PrimaryKey
Table1ForeignKey
Table3ForeignKey
Textfield1
TABLE 3
PrimaryKey
Textfield1
Textfield2
Textfield3
Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.
I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.
From Table 2 I only want the latest record for each record in Table 1.
I want the view to look something like this:
Table 1 PrimaryKey
Table1 Textfield1
Table2 Textfield
Table3 Textfield
In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.
Can anyone enlighten me on the query necessary to get this view?
I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.
we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.
ABSENCES AWAY = Start of an absence from the building RETURN = End of an absence from the building A person during their reisdency will have multiple Absences An absence could start on the same day a previous absence ends Absences vary in length CS CS0-CS10 (each is a different TYPE) of communicatio
[code]....
Query in DESIGN View
SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM CRM_CSLDB.dbo.vCommunication
[code]....
Results from QUERY in DESIGN VIEW
TYPE DATECOMPIDCOMMIDSEQ AWAY2011-02-24 00:00:00.00051747531 RETURN2011-03-31 00:00:00.00051747542 AWAY2011-03-28 00:00:00.00064740681 RETURN2011-04-30 00:00:00.00064752972
SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate, dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM dbo.vCommunication INNER JOIN
Hey All... Got a View question. Have 2 tables: #1 Currencies |CCY_Name|CCY_Code|
#2 Rates |CCY1|CCY2|CCY3|...etc|Active| -> where the Columns CCY# = the Records in #1
How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).
I have much MSACCESS expeience and am new to SQL so here goes...I am trying to create calculated fields in a view, but can't seem tomore complex calculations to work.I can create a new field which does simple arithmetic on two otherfields and rename it so it becomes part of the new view to be queriedby other views, but when I use more complicated constructs I can'tseem to get them to work.For starters:How do you create a new field that has a number based on an IFconstruct based on another field.Example:field1 has state abbrevs and field 2 should have a "1" if field1="NY".In access I do field2:IIF(field1="NY",1,0),How do I accomplish this in MS SQL.Can it be done in the view?Thanks,Brad
Could someone help me by answering the questions below?What's a cursor?What's difference between Query and View?Is a RecordSet just part of a table? Can it be part of a query of view?If the content in a table changed, is it necessary for a old recordset torenew itself by do "Requery()"?Thanks for your help!
Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.
Hello :-)My question is: If I query a partitioned view, but don't know the valuesin the "where x in(<expression>)" clause, i.e.: select * from viewAwhere intVal in(select intVal from tbl1) . Compared to: select * fromviewA where intVal in(5,6).Of course "intVal" is partitioning column.Will this result in an optimized query that searches only the relevanttables?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
Hello All, I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic.
