Suppose I have two tables(Customer and Order) which are as follows:
Code:
Customer
customer_id
first_name
[Code]....
Another thing I am concerned about is that in the line INNER JOIN Order ON Customer.customer_id = Order.customer_id , I have written Customer.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign?
I have the following 2 tables:location:placelftrgt-------------------Europe099England110France1120Italy2130Asia100199London1212staff:namelocLft--------------Edwards0Smith1Leveil11Rossi21Lee12Chan100location uses the Celko hierarchy model.I wish to retrieve for a location the names of all staff within it andthe hierarchy of place associated with that member of staff, eg aquery for Europe should return all staff in Europe, and for Lee I wishto return Lee-London, Lee-England, Lee-Europe etc.I can achieve this using a subquery, ieSELECT name, placeFROM staff, locationWHERE name IN (SELECT nameFROM staff, locationWHERE place='Europe' And locLft>=location.lft AndlocLft<=location.rgt)AND locLft>=lft AND locLft<=rgtBut is this the most efficient way of doing so?Thanks
View 1 Replies View RelatedHi,
Could someone confirm that the following query:
update table set x=1, y=1, z=1 where a = 1 or b = 1 or c = 1 or d = 1
is more efficient than this query:
update table set x=1, y=1, z=1 where e <> 1 and f <> 1 and g <> 1
Thanks!
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
Order by pg.grid_id
I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
Thanks in Advance
Jacx
Hi all,
I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:
CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue
I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....
Finally, I have also run into the problem whereby SQL issues the following error:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEADyerN', error code 0x54b.'
I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.
Many Thanks, Nick
I currently have an ldap query in a stored procedure that is working fine but is checking the 'whenCreated' attribute against a hardcoded data.
SELECT * FROM OpenQuery (
 ADSI, Â
 'SELECT whenCreated,
 whenChanged,
 telephoneNumber,
[Code] .....
How can I modify the hardcoded date (''20130101000000.0Z'') to check against current_date - 7 days?
how to completly disable noise words checking in full-text query?
(noise dictionaries already cleared!)
I want to select one field from a table,but it should on some conditionswhich refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND....Should I use case "select sum(a.amount) from a,b,c,... wherea.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)from select b.field1 from select c.field2 from...."?And which case is moreefficiency?thanks!ÎÒÏë¼ÆËãÒ»¸ö±íÖеÄij¸ö×ֶεĺͣ¬µ«´Ë¼Ç¼ÐèÔÚ´Ó¶à¸ö ±íÖвéѯ´Ë¼Ç¼ÊÇ·ñÂú×ãÌض¨µÄÌõ¼þ¡£ÄÇôÎÒÊÇÓÃselect ..from ...where ..and ..and..and ..and ..»¹ÊÇÓÃselect ..fromselect ..from select ..from ......£¿ÇëÎÊÊÇÄÄÒ»¸öЧÂʸߣ¿Ð»Ð»£¡
View 2 Replies View RelatedHello,I am looking at optimizing site searching on a web application. I have two thoughts on the idea:1. create views with fulltext indexes combining records from multiple tables.2. create a table with an xml column and primary index. I understand the xml column type has the overhead of a BLOB under the hood, but that a primary xml index can "shred" the contents and improve parsing. I also read the xml column is actually searched as a tree, providing some variant of log(n) run time. Does anyone know of good literate on this subject, the more big O notation, runtime analysis types of posts the better.Thanks
View 5 Replies View RelatedHi guys,
Since the project that i'm developing is rapidly increasing, the pages seems to be getting slower everytime you view it. I would like to ask if code below would be efficient enough for several simultaneous request of data or if you have any other suggestions, you are welcome to add:
1 Public Shared Function QueryDatabase(ByVal sql As String) As DataTable2 3 ' SQL Server Connection Object Variable4 Dim _oConnection As SqlConnection5 ' SQL Server Command Object Variable6 Dim _oCommand As SqlCommand7 ' SQL Server Data Adapter Object Variable8 Dim _oAdapter As SqlDataAdapter9 ' DataTable Object Variable (Early Binding)10 Dim _oDataTable As New DataTable11 12 ' Instantiate Connection Object with connection string13 _oConnection = New SqlConnection("Data Source=XXX.XXX.XXX.XXX;Initial Catalog=XXXXXX;User=XXX;Pwd=XXX;")14 ' Instantiate Command Object with SQL String and Connection Object15 _oCommand = New SqlCommand(sql, _oConnection)16 ' Instantiate Data Adapter Object with Command Object17 _oAdapter = New SqlDataAdapter(_oCommand)18 ' Fill the DataTable Object with the retrieve records19 _oAdapter.Fill(_oDataTable)20 21 ' Release resources used by DataAdapter Object22 _oAdapter.Dispose()23 24 ' Release resources used by Command Object25 _oCommand.Dispose()26 27 ' Close the connection of the Connection Object from SQL Server28 _oConnection.Close()29 30 ' Release resources used by Connection Object31 _oConnection.Dispose()32 33 ' Return the retrieve records34 Return _oDataTable35 36 End Function Thanks a lot.
Hey,I am developing a website which will be used by a large number of people so I am concerned about efficiency.Sorry for the three posts but anyone with any info would be appreciated.The database has the following tables: FACILITY-----MEETING ---- | | USERS---- -------- MEETING_INVITE -------- REMINDER | | ---------CONTACTS-------When the user logs in I use there username to access the rest of the tables. I get all of the users information out of the database in one go and store it in a dataset.So when a user accesses there meetings page, I pass the dataset to that page with a server transfer.Question 1 > Is it more efficient to open the database once and access all the information and pass the information to seperate tables or is it more efficient to access the database on the individual pages and thus not passing of information.---------------------------------------------------------------------------------------------------------------In order to access the information I use 6 Select statements in a rowHere is an example of my select statments: SELECT * FROM USERS WHERE email = textbox_emailSELECT FACILITY.* FROM FACILITY, USERS WHERE FACILITY.email = USERS.email AND USERS.email = textbox_emailBy the time I get to the REMINDER table I am combining all the tables and my query is eight lines long.Question 2 > Is there a way of combining the results of a previous select to access information?---------------------------------------------------------------------------------------------------------------Question 3 > What do you think of my table design? The lines represent one to many relationships. If you can give me any tips on databases please do.Thanks for your time,Padraic
View 2 Replies View RelatedHello all,I am developing a website which may be used by a large number of people in the future and I am concerned about performance.
Is it better to have one table with 50, 000 rows or 5,000 tables with 10 rows each?
Is there a way to divide a table in two if the table reaches a certain size?
Is there a limit on the size of tables?
Is there a limit on the number of tables?
Is it possible to create tables from vb.net?
Is it possible to program checks into sql server? For example, could I delete data that has passed a certain date or send an automated email when a time is reached?
Thanks for your time,Padraic
Hey people
I'd be really grateful if someone can help me with this. Could someone explain the following:
If the following code is executed, it runs instantly:
declare @SellItemID numeric (8,0)
select @SellItemID = 5296979
SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )
However, if I use this WHERE clause instead -
WHERE (@SellItemID = 0 OR s.sell_itm_id = @SellItemID)
- it takes 70 micro seconds. When I join a few more tables into the statement, the difference is 4 seconds!
This is an example of a technique I'm using in loads of places - I only want the statement to return all records if the filter is zero, otherwise the matching record only. I think that by using checking the value of the variable in the WHERE clause, a table scan is used instead of an index. This seems nonsensical since the variable is effectively a constant. Wrapping the entire select statement with an IF or CASE works, but when I've got 10 filters I'd have to 100 select statements.
I DON'T GET IT!! There must be a simple answer, HELP!!
Jo
PS this problem seems to occur both in 6.5 and 7.0
Hi All,
I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.
Simply, is it better to update a table via an UPDATE query or Recordset manipulation?
I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?
i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?
It's a MS Access XP app with ADO 2.8.
My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.
I appreciate your help!
Thanks,
Warren
I am using nested cursors in my script below, and wonder if there is a more efficient way please?
USE ar
GO
DECLARE @mortgage INT,
@mortgage_sequence int,
@getMortgage CURSOR,
@notes_1 varchar(MAX),
@notes_2 varchar(MAX),
@notes_3 varchar(MAX),
@notes_4 varchar(MAX),
@notes_5 varchar(MAX),
@notes_6 varchar(MAX),
@notes_7 varchar(MAX),
@notes_8 varchar(MAX),
@notes_9 varchar(MAX),
@notes_10 varchar(MAX),
@notes_11 varchar(MAX),
@notes_12 varchar(MAX),
@notesComplete varchar(MAX),
@addedUser varchar(255),
@addedDate varchar(255),
@amendedUser varchar(255),
@amendedDate varchar(255),
@sequence int,
@getDetail CURSOR
SET @getMortgage = CURSOR FOR
SELECT DISTINCT Mortgage_Number, Mortgage_Note_Sequence_No
FROM format_additional_notes
GROUP BY Mortgage_Number, Mortgage_Note_Sequence_No
ORDER BY Mortgage_Number ASC
OPEN @getMortgage
FETCH NEXT
FROM @getMortgage INTO @mortgage, @mortgage_sequence
WHILE @@FETCH_STATUS = 0
BEGIN
SET @getDetail = CURSOR FOR
SELECT ltrim(rtrim(Additional_Text_1)),
ltrim(rtrim(Additional_Text_2)),
ltrim(rtrim(Additional_Text_3)),
ltrim(rtrim(Additional_Text_4)),
ltrim(rtrim(Additional_Text_5)),
ltrim(rtrim(Additional_Text_6)),
ltrim(rtrim(Additional_Text_7)),
ltrim(rtrim(Additional_Text_8)),
ltrim(rtrim(Additional_Text_9)),
ltrim(rtrim(Additional_Text_10)),
ltrim(rtrim(Additional_Text_11)),
ltrim(rtrim(Additional_Text_12)),
Mortgage_Note_Sequence_No,
Extra_Added_by_User,
Extra_Added_on_Date,
Extra_Amended_By_User,
Extra_Amended_By_Date
FROM format_additional_notes
WHERE Mortgage_Number = @mortgage AND Mortgage_Note_Sequence_No = @mortgage_sequence
ORDER BY Mortgage_Note_Sequence_No
OPEN @getDetail
SET @notesComplete = ''
FETCH NEXT FROM @getDetail INTO @notes_1,
@notes_2,
@notes_3,
@notes_4,
@notes_5,
@notes_6,
@notes_7,
@notes_8,
@notes_9,
@notes_10,
@notes_11,
@notes_12,
@sequence,
@addedUser,
@addedDate,
@amendedUser,
@AmendedDate
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @notesComplete = @notesComplete +
ISNULL(@notes_1,'') + ' ' +
ISNULL(@notes_2,'') + ' ' +
ISNULL(@notes_3,'') + ' ' +
ISNULL(@notes_4,'') + ' ' +
ISNULL(@notes_5,'') + ' ' +
ISNULL(@notes_6,'') + ' ' +
ISNULL(@notes_7,'') + ' ' +
ISNULL(@notes_8,'') + ' ' +
ISNULL(@notes_9,'') + ' ' +
ISNULL(@notes_10,'') + ' ' +
ISNULL(@notes_11,'') + ' ' +
ISNULL(@notes_12,'')
FETCH NEXT FROM @getDetail INTO @notes_1,
@notes_2,
@notes_3,
@notes_4,
@notes_5,
@notes_6,
@notes_7,
@notes_8,
@notes_9,
@notes_10,
@notes_11,
@notes_12,
@sequence,
@addedUser,
@addedDate,
@amendedUser,
@AmendedDate
END
INSERT INTO format_additional_notes_1
(Mortgage_Number,
Mortgage_Note_Sequence_No,
Additional_Text,
Extra_Added_By_User,
Extra_Added_on_Date,
Extra_Amended_By_User,
Extra_Amended_By_Date)
VALUES
( @mortgage,
@sequence,
@notesComplete,
@addedUser,
@addedDate,
@amendedUser,
@amendedDate)
CLOSE @getDetail
DEALLOCATE @getDetail
FETCH NEXT
FROM @getMortgage INTO @mortgage, @mortgage_sequence
END
CLOSE @getMortgage
DEALLOCATE @getMortgage
GO
I would like to use MVJ's formula for creating a date table.
I would like to use it with our main ERP database. However, I am reluctant to make changes to it because I fear that at some point when we upgrade that software and it's database that the upgrade program will delete my table.
So, here is my question. Performance wise, does it matter whether I add the date table to our ERP database or if I create another database (on the same server) for the custom date table? Does linking between databases take substantially longer than linking within the same database?
Hi,
okay so I'm refactoring some code at the moment. At the moment, I'm working on a search screen. This search screen lets the user enter a number of criterias, I'm working on drags data from a view and then programmatically filters it according to the search filters.
This is obviously inefficent and non-scalable as the view drags out every entry and returns to the data layer, which then filters it.
I'm wondering what the best way to refactor this? i'm thinking the best way is to tell the db what to filter on, so it'll only drag out the right amount of data.
Therefore, should I keep the view? Is there any way of entering parameters into views or am i going to need to change this into a stored proc?
hi,Allcould you tell me which case is more efficiency?(my tables have no index)And does it has any else case more efficiency?case1:"select sum(Invoice_Production.Quantity) from Invoice_Production,(select[dat_Item].ItemCode from [dat_Item],(select [dat_MachineType].MachineTypeIDfrom [dat_MachineType]"&subQuery&") as T3 where [dat_Item].MachineTypeID =T3.machinetypeid) as T1,(select [Invoice].InvoiceNo from Invoice,(select[users].user_id from [users] where [Users].User_ID = '"& rs2(0) &"') as T4where T4.User_ID = invoice.dealerno and Invoice.Cyear >= "&startYear&" andInvoice.Cyear <= "&endYear&" and Invoice.Cmonth >= "&startMonth&" andInvoice.Cmonth <= "&endMonth&") as T2 where invoice_production.ItemCode =T1.ItemCode and T2.invoiceno = invoice_production.invoiceno"case2:"select sum(Invoice_Production.Quantity) from[Invoice_Production],[Invoice],[dat_MachineType],[dat_Item],[users] where[users].user_id = [invoice].DealerNo and [dat_Item].ItemCode =[Invoice_Production].ItemCode and [dat_Item].MachineTypeID =[dat_MachineType].MachineTypeID and [Invoice_Production].InvoiceNo =[Invoice].InvoiceNo and [Users].User_ID = '"& rs2(0) &"' and Invoice.Cyear
Quote:
How efficient is ti to use join views in a database?I'm developing an e-commerce system and using join views to join theproduct, product category and product review tables and wondering if thiswould have any adverse effect on performance.Thanks in advance
View 3 Replies View Related
Greetings all,
I need to determine a hierarchy from a table with EmpID's and SupID's. Basically, the President doesn't have a SupID so it will be null. I need to determine programatically the hierarchy to keep it simple.
I have code that works and I was hoping for advice on optimizing it 'cuz it uses a cursor.
Also, It only deals with less than 300 records.
Code Block
CREATE TABLE Employee(fName varchar(30), EmpID int, SupID int)
INSERT INTO Employee SELECT 'Adam', 1, 4
INSERT INTO Employee SELECT 'Joe', 2, 4
INSERT INTO Employee SELECT 'John', 3, 4
INSERT INTO Employee SELECT 'Frank', 4, 10
INSERT INTO Employee SELECT 'Jane', 5, 10
INSERT INTO Employee SELECT 'Kristy', 6, 10
INSERT INTO Employee SELECT 'Angie', 10, 11
INSERT INTO Employee SELECT 'Ron', 11, NULL
--=====================================================================================
-- CODE
--=====================================================================================
CREATE TABLE #temp(Hierarchy int, myName varchar(30), SupID int, EmpID int)
INSERT INTO #temp SELECT 1, fName, SupID, EmpID FROM Employee WHERE SupID IS NULL
--NULL SupID means that they are at the top most branch
DECLARE @Counter int --Counter is used to increment
SET @Counter = 1
DECLARE MY_CURSOR Cursor
FOR
SELECT SupID, EmpID, fName
FROM Employee
ORDER BY SupID --ORDER BY SupID to bring NULLs to top
Open My_Cursor
DECLARE @EmpID int, @SupID int, @Name varchar(30)
FETCH NEXT FROM MY_Cursor INTO @EmpID, @SupID, @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Counter = (SELECT MAX(Hierarchy) FROM #temp) + 1 --Get the highest hierarchy ID and increment by 1
INSERT INTO #temp
SELECT @Counter, fName, SupID, EmpID
FROM Employee
WHERE SupID IN (SELECT EmpID FROM #temp WHERE EmpID = @SupID)
FETCH NEXT FROM MY_CURSOR INTO @EmpID, @SupID, @Name
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT * FROM #temp
DROP TABLE #temp
Thanks in advance,
Adam
Howdy folks!
I've got a database that needs to run 24/7. I'm looking into maintanence options and wanted to run the following by y'all:
Ok, I've read the MSDN "Maintaining databases" article and noticed the following statement about autoshrinking: "This technique uses almost no processor time and memory". I also searched these forums and found that many users say autoshrinking heavily lags down sql transfers. So who's right? And if it does lag transfers, by how much?
Another question I have about autoshrink is fragmentation. It would seem to me that over time solely depending on autoshrink would cripple a server in terms of fragmentation; is this the case?
Also, does autoshrink (or manual shrinking or compacting) update the statistics?
Final question!!! I'm programming in native c++, is there a way for me to run commands such as "DBCC SHRINKDATABASE" in native OLE DB code?
Thanks!
I have a website that is probably going to hold a sizable amount of data. The data will be specific to groups of users based on login credentials. Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database?
Any thoughts on the topic would be appreciated.
Thanks
I'm working on a "comments" section for our application suite.
My thoughts are to have 1 Comments table, which is then linked to a comment Log table.
For each section: Item, Group, Section, User, Package (these can all have comments on them) I will link them to the comments in a many to many relationship.
Example:
Comments Table-CommentID-UserID
UserComments-UserID-CommentID
User_Table-UserID....
Would doing that be more efficient than having 1 seperate comments table, and log table for each area I want to have comments?
Hello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason
View 1 Replies View RelatedHi all.
I am new to reporting services and I am having an efficiency problem when loading my report.
I would like to know how Reporting Services handles its datasets.
1: Lets say I have 3 parameters. All set to retrieve data from the same dataset. Does reporting services execute the Query 3 times to get the results for each parameter ? If so, is there a way around this ? I am having a great performance hit with this if it is the case.
2: I am also having an issue with a data processing extensions, when my multi-valued parameter reads the fields from the dataset.. it inserts duplicates and not distinct values, Do i need to explicitly select distinct values in the data processing extension or should Reporting Services automatically do this ?
Any help is greatly appreciated.
Regards,
Neil
************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************
Well met,
Let's say I have a web form that allows users to insert and update records in a SQL database. Is it better to set empty web controls (textbox, etc.) to DBNull or let it go as an empty string into the database?
Example code:
if (tboxNAME.Text == "")
{
sqlCommand1.Parameters["@NAME"].Value = DBNull.Value;
}
else
{
sqlCommand1.Parameters["@NAME"].Value = tboxNAME.Text;
}
Is the above overkill? It seems like it would be a good idea to set fields which the user empties back to Null rather than an empty string.
Thanks,
-Tony
Hello,
I regularly create stored procedures and use them like functions within other stored procedures.
I've never had any difficulty but then I never ran any metrics on it.
Does anyone know if there is an efficiency difference between that approach and just doing an inline query? How much of a difference is it? minimal? impractically large?
For example, if I define an sp like:
create proc isValidUser @userID int , @result int OUTPUT as
if exists(select * from user where userid = @userID)
set @result = 1
I'm having problems testing the effectiveness of changes I'm making to a stored_procedure. I need to test the runtime, but each time I run the sp, it gets progressively faster because the data has already been cached. Is there a way for me to clear the data from cache before each run? I can't stop SQL Server.
View 2 Replies View RelatedHi,
I have two servers that i want to create a SQL RS report on.
On one server there is an HR database with our staff details, on the other server there is a database of assets.
In order to report on the assets assigned to each user i am thinking that i will have to :
1) link the servers
2) create a view in the HR database exposing the fields needed
3) create a view in the assets database exposing the assets information joined to the view from the other server
4) create my reports on the view on the assets server.
is this right or am i barking up the wrong tree?
My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases. I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.Is he all wet, or would there be gains to be made?TIA
View 7 Replies View Related