I Have A Question With Related To Performence
Jun 25, 2006
i have a single souce table , Table a With contiains records for two
different entries for the same vendor by different accounting
instructions,
BidId = 10,Person Name=ABC,PersonBidAmt=$100
BidId = 11,Person Name=ABC,PersonBidAmt=$110
now the recors are to be shown as
Original Rcds Vs IntutiveRecords
OrName,OrAmt,IntName,IntAmt
ABC,100,ABC,110
for this reason i use
Select A.name,A.Amt,B.name,B.Amt
from A
leftOuterjoin B -- There are many reasons why i do a left outer join
because i may not have the record in the b side at all
but this is causing a very big performence issue as this view is very
huge.31,000 records on each side is taking 2 minits to work.
Could any one help me with this.
View 4 Replies
ADVERTISEMENT
Sep 14, 2006
suresh writes "Hi
This is suresh,
I want to know some information about Dynamic sql
what its performense wise?
Difference bitween the proc and dynamic sql?
Advantages of dynamic sql?"
View 1 Replies
View Related
Dec 5, 2007
Here each record have start and ending time ,i want to find the timeleft between currenttime and endtime for each and every records.
Eg: This record end within "10day 12hrs 10mins"
View 4 Replies
View Related
Sep 5, 2006
Is there any difference between a nText field value='' and value=NULL ?
View 4 Replies
View Related
Sep 13, 2007
Hello,
I have the following view SQL ( LATESTRECALL_v, for communication purposes ):
SELECT REC.RECKEY, REC.RECSTM, REC.RECUID, REC.RECREC, REC.RECLNN, REC.RECCCD, REC.RECSKU, REC.RECSKD, REC.RECPEC,
REC.RECPRL, REC.RECQTY, REC.RECTGQ, REC.RECTAG, REC.RECASN, REC.RECRLC, REC.RECPLC, REC.RECNTS, REC.RECRST,
REC.RECLOT, REC.RECARF, REC.RECVEN, REC.RECIBO, REC.RECSRN, REC.RECPAR, REC.RECINS, REC.RECSDTR, REC.RECDEA,
REC.RECUOI, REC.RECFPL, REC.RECTDA, REC.RECUMN, REC.RECRQT, REC.RECCID, REC.RECCTN, REC.RECFPC, REC.RECEPU,
REC.RECSCQ, REC.RECLOG, REC.RECSTS, REC.RECTDV, REC.RECSHDR, REC.RECRRN, REC.RECWDF, REC.RECWWF, REC.RECSLT,
REC.RECSQT, REC.RECSTG, REC.RECRTG, REC.RECPSN, REC.RECIFL, REC.RECEXPR, REC.RECMFDR, REC.RECMFGR, REC.RECCNO,
REC.RECSC1, REC.RECSC2, REC.RECSC3, REC.RECSC4, REC.RECSC5, REC.RECSC6, REC.RECSC7, REC.RECSC8, REC.RECSC9,
REC.RECSD1, REC.RECST1, REC.RECSD2, REC.RECST2, REC.RECSD3, REC.RECST3, REC.RECSQ1, REC.RECSQ2, REC.RECSQ3,
REC.RECSQ4, REC.RECSQ5, REC.RECSQ6, REC.RECSDU1, REC.RECSDU2, REC.RECRRC, REC.RECICS, REC.RECJOBS
FROM dbo.REC, dbo.LATESTRECKEY_V
WHERE REC.RECKEY = LATESTRECKEY_V.LATEST_RECKEY
Note that it refers to view LATESTRECKEY_V, which is defined below ( the SQL ):
SELECT REC.RECSKU, REC.RECPRL, max(REC.RECKEY) AS latest_reckey
FROM dbo.REC, dbo.LATESTREC_V
WHERE
REC.RECSKU = LATESTREC_V.RECSKU AND
REC.RECPRL = LATESTREC_V.RECPRL AND
sysdb.ssma_oracle.to_char_date(REC.RECSDTR, 'YYYYMMDD') + REC.RECSTM = LATESTREC_V.LATEST_DATE_TIME
GROUP BY REC.RECSKU, REC.RECPRL
Note that it refers to view LATESTREC_V, which is defined below ( the SQL ):
SELECT REC.RECSKU, REC.RECPRL, max(sysdb.ssma_oracle.to_char_date(REC.RECSDTR, 'YYYYMMDD') + REC.RECSTM) AS latest_date
time
FROM dbo.REC
GROUP BY REC.RECSKU, REC.RECPRL
Now, there already is a key on the REC table, columns RECSKU and RECPRL. Adding that index sped things up quite a bit. But this setup is still very enefficient. I cannot apply the indexed view concept to LATESTRECKEY_V or LATESTREC_V because they use "max". I suppose I could apply it to the first view, but since that view depends on the other 2 views, I am not convinced it will improve my performance. Any ideas on improving such a situation? Thanks for your time.
Tony
View 2 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Nov 23, 2004
Hi,
Is there a format function in MDX which will help me solve the following
scenario:
One of my measures is Net Sales and I have tried all different formats
in Analysis Services so that it shows without the decimal places i.e.
instead of showing the Net Sales for any selected combination of
Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my
requirement. However, in my front-end (Microsoft Data Analyzer), I am
still getting it as 123,456.04 (even when the same in Analysis services,
i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data
Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me
get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Many TIA
View 2 Replies
View Related
Aug 5, 2006
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
View 3 Replies
View Related
Nov 20, 2006
I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";
SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231");
SqlCommand cmd = new SqlCommand(sql, myConn);
SqlDataReader dr;
myConn.Open();
dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.
View 8 Replies
View Related
Jan 5, 2008
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
View 16 Replies
View Related
Feb 12, 2008
I want to make simple database application but I want that I make just one transaction with the database..
If I have , say 10 insert queries, i want to transact with the database just once.
Somebody told me this could be done by 'containers' or 'data transfer objects'
So please, somebody help..
View 1 Replies
View Related
Mar 10, 2005
Hi,
I made a DTS which appends data coming infrom a view to an exisiting table.So far no problem and all goes well.
I am facing a problem due to the format of the date that is coming in (getting appended) and while going through BOL, came across the following topic:
mk:@MSITStore:C:Program%20FilesMicrosoft%20SQL%2 0Server80ToolsBookshowtosql.chm::/ht_dts_trns_97ou.htm
I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).
Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.
Many TIA
View 4 Replies
View Related
Jul 24, 2007
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
View 1 Replies
View Related
Jun 20, 2008
select @xml = bulkcolumn from openrowset(bulk 'C:Documents and
SettingsKasiDesktopewsrss.xml' , single_blob) as channel
here after bulk instead of giving path, we have to give parameter so that the paramter takes the value from the table.
the tables contains paths of xml files
View 2 Replies
View Related
Jan 4, 2006
there is a prblem with data in pivoting the table.
problem is like this--
there is some data 'xy' and some data 'xy '. when i m giving 'xy' as a pivot key value it doesent recognise 'xy ' and viseversa..
i can't reduce the size of the datatype coz there is some data of diffrent size as 'abcd'.
this data is loaded from excel sheet to sql sever table.
wht can i do for this problem.
is there any method to truncate the indivisual data, i m using nvarchar datatype for this.
View 2 Replies
View Related
Jan 12, 2007
Hi friends,
I am backing up the database using the Database Maintenance Plan.
Everything is in place. I want to save the file name in the format yyyy-mm-dd_finbck.bak. how can i assign the date from here itself ? this should be done through the DB maintenance plan only.
is it possible ? can we assign the date to the backup file through the DB maintenence plan ?
kindly guide.
Regards,
Amit
View 4 Replies
View Related
Dec 10, 2007
Dear All,
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
Thanks in Advance.
View 16 Replies
View Related
Jul 23, 2005
Is it possible to retrieve all tables that a given one is related tovia foreign keys?
View 2 Replies
View Related
Aug 11, 2005
We had been running SQL Server without any control of security (sincethe company is very small -100 employees). All of us know the adminpassword and has been accessing the database as admin. Our databaseserver crashed due to hardware failure twice last month and we lost alot of important data. Now the management is taking the control ofserver access seriously.SQL Enterprise manager is installed on many PCs and any one can deleteany database with a right click.My question is:1. Can the enterprise manager be installed on client's PC with alimited right (or as a user not as admin)?We need to limit the user's access of using the Enterprise Manager.In other words, how can we set this up for different users.2. How can we keep running SQL Server if one server fails?Clustering or Replication or Mirroring? OI would highly appreciate if you could direct me to any website orresources on how to set up security of SQL Server (2000 with the latestservice pack).Thanks a million in advance.Best regards,Mamun
View 2 Replies
View Related
Oct 26, 2006
Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please
Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)
View 1 Replies
View Related
Jul 25, 2007
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
View 1 Replies
View Related
Mar 20, 2008
Hi,
I have a data including "year", "month" and "day" columns and I'm using these columns as parameter to create a report. I'm using the parameters successfully in my report. I import all possible year-month-day combinations but in my data there are some unmatched combinations. (For ex, for 2007 there's just last 3 months of the year, and some days are missing) To make the report more affective, I wanna make the parameters dynamic; when I choose 2007 in year-combobox I wanna see just the related months (not all 12) and the related days after I choose the month. Is it possible in reporting services?
View 9 Replies
View Related
Mar 24, 2004
Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
View 2 Replies
View Related
Nov 26, 2004
Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.
The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.
I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.
Thanks.
Regards,
Sumis
View 1 Replies
View Related
Sep 12, 2005
I have 2 related tables I want to include in a single full text search. How would I go about setting this up?
View 1 Replies
View Related
Jan 31, 2006
Here is the first table:CREATE TABLE "Games"("ID" int,"Title" varchar(30),"Popularity" int)Here is the second table:CREATE TABLE "Related"("ID" int,"rID" int)Here is the data for the first table:INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)Here is the data for the second table:INSERT INTO Related (ID, rID) VALUES (1,2)INSERT INTO Related (ID, rID) VALUES (2,1)INSERT INTO Related (ID, rID) VALUES (1,3)INSERT INTO Related (ID, rID) VALUES (2,3)INSERT INTO Related (ID, rID) VALUES (3,1)INSERT INTO Related (ID, rID) VALUES (3,2)INSERT INTO Related (ID, rID) VALUES (6,7)INSERT INTO Related (ID, rID) VALUES (7,6)Now, lets say a person wants to grab the top three results:SET ROWCOUNT 3Select * from Game ORDER BY PopularityResult Set:1 Tag Dodgeball 102 Freeball 103 Dr. Dodgeball 10But,
what if this person doesn't want to play three related games in a row?
This is my dilemma. How do I get the following result set:1 Tag Dodgeball 102 Kickball 83 Fooseball 8The
related table tells which games are related to which, but how do I get
the select statement to realize that it should not select any games
that are related once it already has one in the result set?I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.Pseudo-Code might look something like this:SET ROWCOUNT 3Select * from Game ORDER BY Popularity (where ID !=rID)Please help. I've banging my head against this one for quite some time.Respectfully,David.
View 1 Replies
View Related
Mar 2, 2006
Hello EveryOne,I have a program which insert the Data into table Fine. But the problem is that whenever expecption is raised the auto increament id is increases..i want that user enter the record and without error the auto increament id is 1 and when second user enter the record on table and exception raised and next time he entered again then his record auto increament id is 3 why? But record is not entered only the expecption is raised.How to resolve this problem to make cosistency in record of other tables.? Help me
View 2 Replies
View Related
Mar 8, 2006
Plz help me out making a SELECT for my imagegallery.
Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all.
Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId}
In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath]
To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle]
To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select
And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID()
-But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
View 4 Replies
View Related
Apr 26, 2006
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
This is the error i get every now and then when i attempt to browse to a page. It seems that i am getting this error as its trying to connection to a sql express database??? Only i have set my project up to use sql server 2000 and i am using that DB but every now and then i get this error. Im not sure what else i need to do? Any ideas?
View 4 Replies
View Related
Aug 20, 2002
Hi All,
I've created a DTS package with 2 connections to 2 servers. The operation transfers a db in server1 to server2. I have to set the sa password for each of the server (when setting up the connections).
Our policy is to change the server password regularly. This creates problem as I have to change the password setting at each of the connections. If there are 100 connections, I have to change it 100 times. Is there a way to avoid this so that we can avoid this tedious work.
TIA
Rgds,
scyeo
View 2 Replies
View Related
Sep 8, 2006
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter
Set counter = currentpage
If counter > lastpage
Goto task exit loop
Else perform task - (insert string into table)
Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int
DECLARE @Current string
DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage
SET @Count= dbSL.tblPaging._colPagingCount
SET @Last= dbSL.tblPaging._colLastPage
WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN
PRINT 'Hello World'
SET @Count = @Count +1
END
Any help much appreciatted.
Thanks
nme
View 1 Replies
View Related
Dec 3, 2004
Hi all,
Can a table be created within a trigger? I looked in books online and found the list of transact-sql statements that are not allowed in a trigger. Create table is not one of them, so I assume that it can be done within a trigger. Correct? Thanks for your help.
View 3 Replies
View Related
Oct 26, 2005
Hi,
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28
I would like to know the Package name related to.
Any help would be very appreciated.
Regards
View 3 Replies
View Related