EXPERT: Implement Dijkstra's Algorithm -&> Need Lots Of Help Implementing!
Jan 9, 2008
This is such a complex question and I'm 99.9% sure it requires usage of Dijkstra's algorithm in order to determine the shortest path. :(
I have tried to build this myself (yes, I've viewed enough examples on the web, but since they dont exactly do what I want AND I'm rather new to this advanced SQL AND my boss would really like this asap I feel forced to call upon the community)
Basically I need a query which analyzes the relationships between 2 persons and returns the shortest path(S!) I have provided the data that is required to perform any tests below. The example I provide match with the given data.
I know for sure that such a query has been written before since for example LinkedIN uses something similar...so if anyone has this off the shelf for me great!
If not, I would really really appreciate it if someone could provide a completely worked out example. I'll even give special thanks to that person on our future website :)
So, many thanks ahead for whoever takes up this challenge! :)
CASE:
-----------------------------------------------------------------------------
I have tables with friend relationships and tables with userdata.
Lets say im logged in as Peter (usercode 5).
Now if I (as user Peter) view the profile of Andre (usercode 51), I want to determine the relationship that exists between me and Andre.
When the users would have a direct relationship, eg. between Peter (5) and John (6) I want returned:
col1 col2 col3 col4
5 Peter 6 John
When the users would have a indirect relationship, witch EXACTLY 1 person in between, like between John (6) and Jack (48).
So I can go from John to Jack in exactly 2 steps via multiple persons, in this case I want the following rows returned (max 4):
col1 col2 col3 col4 col5 col6
6 John 11 Hans 48 Jack
6 John 15 Hans 48 Jack
When the users would have a indirect relationship, witch MORE than 1 persons in between, like between Peter (5) and Andre (48), I want returned:
col1 col2 col3 col4 col5 col6 col7 col8
5 Peter 11 Hans 48 Jack 51 Andre
In any case when there are multiple paths from person A to person B, I only want the shortest paths returned to a maximum of 4
Since this query will be called may times by different users at the same time concurrency issues also need to be taken into account (e.g. usage of temp tables)
with the entire query the maximum amount of steps that should be checked is 6, so maximum 6 persons in between 2 persons.
So if a viewed user is more than 6 steps away from the viewing user I want no results returned.
E.g. when Peter (5) views the profile of Simon (7), no relationship exists through any other person, and an empty dataset should be returned.
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ? Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
I want to make it possible that I can see the path from one user to another and via whom the route went:I want to pass the startnode and the endnode and then retreive the route between them.In case I go from 5 to 15 (see table below), I want to have the following returned:5-6-155-7-15In case I go from 5 to 18 I want to have returned:5-6-15-9-185-7-15-9-18always up to a maximum distance of 6 steps.I have the following table definitiontblFriendsOwnerCode intFriendCode intthis table contains content like:
5 6
5 11
5 7
6 5
6 12
6 15
6 11
7 15
7 5
9 15
9 18
11 5
11 6
12 6
13 6
15 6
15 7
15 9
18 9 As you can see, I store each relationship twice.I have created the following SP to retreive the info, but dont know how I can get from these results to the results I desire as described above....STORED PROCEDURE set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER PROCEDURE [dbo].[myspShortestPath]--shortest path based on algorythm of Dijkstra @ViewingUserCode int, @ViewedUserCode int, @MaxDistance int=100, @MinDistance intASBEGIN -- Automatically rollback the transaction if something goes wrong. SET XACT_ABORT ON BEGIN TRAN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Create a temporary table for storing the estimates as the algorithm runs CREATE TABLE #UserList ( UserCode Int NOT NULL, -- The City Id UserName nvarchar(50), Estimate Int NOT NULL, -- What is the distance to this city, so far? Predecessor nvarchar(max), -- The city we came from to get to this city with this distance. PredecessorCodes nvarchar(max), Done bit NOT NULL -- Are we done with this city yet (is the estimate the final distance)? ) -- Fill the temporary table with initial data INSERT INTO #UserList (UserCode, UserName, Estimate, Predecessor,PredecessorCodes, Done) SELECT UserCode, UserName, 2147483647, '', '', 0 FROM tblUserData -- Set the estimate for the city we start in to be 0. UPDATE #UserList SET Estimate = 0 WHERE UserCode = @ViewingUserCode IF @@rowcount <> 1 BEGIN RAISERROR ('Couldn''t set start user', 11, 1) ROLLBACK TRAN RETURN END DECLARE @FromUser Int, @CurrentEstimate Int,@FromUserName nvarchar(50) -- Run the algorithm until we decide that we are finished WHILE 1=1 BEGIN -- Reset the variable, so we can detect getting no records in the next step. SELECT @FromUser = NULL -- Select the UserCode and current estimate for a city not done, with the lowest estimate. SELECT TOP 1 @FromUser = UserCode, @FromUserName = UserName, @CurrentEstimate = Estimate FROM #UserList WHERE Done = 0 AND Estimate < 2147483647 -- Stop if we have no more unvisited, reachable cities. IF @FromUser IS NULL BREAK -- We are now done with this city. UPDATE #UserList SET Done = 1 WHERE UserCode = @FromUser -- Update the estimates to all neighbour cities of this one (all the cities -- there are roads to from this city). Only update the estimate if the new -- proposal (to go via the current city) is better (lower). UPDATE #UserList SET #UserList.Estimate = @CurrentEstimate + 1, --keep the other predecessors as well #UserList.Predecessor = replace(@FromUserName, ' ', '')+','+#UserList.Predecessor, #UserList.PredecessorCodes = replace(str(@FromUser), ' ', '')+','+#UserList.PredecessorCodes FROM #UserList INNER JOIN tblFriends ON #UserList.UserCode = tblFriends.UserCodeFriend WHERE tblFriends.UserCodeOwner = @FromUser AND (@CurrentEstimate + 1) <= #UserList.Estimate END SELECT ud1.UserName AS UserNameFriend,ud1.UserCode, Estimate AS Distance, PredecessorCodes, Predecessor FROM #UserList INNER JOIN tblUserData ud1 ON #UserList.UserCode = ud1.UserCode WHERE #UserList.Estimate<=@MaxDistance AND #UserList.Estimate>=@MinDistance ORDER BY Distance ASC -- Drop the temp table. DROP TABLE #UserList COMMIT TRAN END
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software). When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass MyClass.Property1 = "Hello" MyClass.Property2 = Unit.Pixel(100) MyClass.Property3 = 100Or even a control:Dim MyLabel As Label MyLabel.Id = "MyLabel" MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle. Many thanks
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message: Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1 if (@@error <> 0) begin Print 'An error occurred reading the temporary import table' goto exitpoint end
select @error = @@error,@rowcount=@@rowcount if @error <> 0 begin print ' Error updating - Error number '+@error+'. Rolling back' --this reverses your updates rollback --this jumps to the end goto exitpoint end
print @rowcount+' rows updated'
insert into dbo.Orderposreg (Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring) select Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring from dbo.Temp1 where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg) select @error = @@error,@rowcount=@@rowcount if @error <> 0 begin print ' Error inserting - Error number '+@error print ' Rolling back updates and inserts' --this reverses your updates and inserts rollback --this jumps to the end goto exitpoint end
print @rowcount+' rows were inserted' --this saves your data commit
Hi ! I have been worked with VC++, MS SQL SERVER, Transact-SQL for 3 years. I made an axtended stored procedure (xp_test) which returns an recordset. From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return the recordset that it is returned by xp_test after its execution. Something like that :
CREATE function dbo.MyTest ( ) RETURNS @table ... AS BEGIN exec master.. xp_test table1 output -- can I do this ?
I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system. But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine. Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID. My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?) My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
I have a table describing items with 3 rows: ID : its an identity Primary key Type : nvarchar string for itemtype ItemData : numeric data for an item
I would like to create a query to get the results where each returned row contains - Type - number of items for the given type - concatenated string of the ItemData numbers for all items for the given type
SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type
but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?
First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is : --------------------------------------------- CREATE PROCEDURE Top1 @tblname Varchar(255) AS
EXEC ( 'SELECT TOP 1 * FROM ' + @tblname + ' WHERE Status="OLD" ORDER BY ID DESC') GO ----------------------------------------------- The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors : -------------------------------------------------------------------------------------------------- CREATE PROCEDURE AddNewStory @tblname varchar(50), @title varchar(50), @Content varchar(5000) , @Picture varchar(200) , @date datetime AS
Server: Msg 128, Level 15, State 1, Line 2 The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. Stored Procedure: db1sql.dbo.AddNewStory Return Code = 0
Idea here is to output results of a stored proc to a text file..
I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach? Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{ public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{ public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years' SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy' SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
Hello all,I have a database in SQL Server that should save data from a CRM-likeapplication.The database consists of tables like products, services, customers,partners etc. Problem is that the users should be able to find theseitems on different properties and with or without substring finding(SQL: LIKE). Example: I want the users to be able to find a customer,providing a customerID, but also providing a customername, zipcode orjust a part of those strings.This will result in a lot of queries. I bet there are some nicesolutions to this, since I will not be the first with this situation.If anyone can help, please.Thank you in advance.Regards,Freek Versteijn
I have two tables - T_Assets and T_Inspections. I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL: SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection) FROM T_Assets INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this. Jagdip
Hi All Professionals Programmers, I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can. Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc. you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships. Hope you have understood what i am going to say and need advice of professional and expert user to resolve it. Any concise quality material like articles, white paper etc will also be suitable for me. Thanks in Advance
CREATE PROCEDURE SP_ProcessAdminResults(@UserID nvarchar(100)) As
Declare @ReqID as integer; Declare @Assaignee as integer; Declare @TechName as nvarchar(200); Declare @OpenDate as datetime; Declare @GroupID as integer; Declare @DateActiontaken as datetime; Declare @AssignedDate as datetime; Declare @ActionDays as integer; Declare @OpenDays as integer;
declare GroupCursor cursor local scroll static for select [ReqID],[Assignee],FullName,[OpenDate],[GroupID],[DateActionTaken] from V_ProcessRequestMaster where UserID=@UserID and [Status]<>'CL' order by ReqID open GroupCursor fetch first from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken while (@@fetch_status <>-1) begin select @AssignedDate= min(DateLogged) from LOGMASTER where analyst=@Assaignee and reqMasterID=@ReqID and UserID=@UserID and Type='INIT' If @AssignedDate is null Begin select @AssignedDate=dbo.GetActionDate(LTRIM(Rtrim(@TechN ame)),@ReqID,@UserID) End
If @AssignedDate is null BEGIN set @ActionDays=0 END Else BEGIN If @GroupID=438030 BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,5 ,6) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),5,6) END Else BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,1 ,7) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),1,7) END
END If @ActionDays<>0 set @ActionDays=@ActionDays-1 If @OpenDays<>0 set @OpenDays=@OpenDays-1
update requestmaster set DateAssigned=@AssignedDate, ActionDays=@ActionDays, OpenDays=@OpenDays where ReqID=@ReqID and UserID=@UserID fetch next from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken end GO
The master table contains like 600,000 odd records and when i put a cursor and process the results where i need to get the business days between assigned date and action taken date the query take lot of time and eventually the front end crashes saying Time OUT expired.
Looking for an expert solution to simplify that query.
Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are: 1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type? 2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?
This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?
i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK
All: This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.
So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.
DTS the ones to keep to another table, drop the old and rename the new table? Bulk copy out, truncate and bring back in? DTS to text, truncate and import back? Other ways?
Never worked with such a large table and need a little experienced guidance.
I am planning an application where ~1000 companies will be accessing data. Should I use a key to identify the company and place all data in one table i.e (WHERE company =123) or should the application create company specific tables i.e should I have 1000 small tables with 100 records in each, or one table with 100,000 records?
You know how there are lots of hosted applications out there, many of them provide you with your own database (not shared).
1. If a server has 1K databases on it, will this slow down the server just due to the # of databases? (each user has their own database, but they won't be accessing it that much really).
A seperate database is required for security purposes usually.
