I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.
This is the thread about the problem: http://forums.asp.net/thread/1665023.aspx
I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.
static object Locked = new object();
public object ExecuteCommand(string queryStr, string type)
{
//*************************************************************************************//
// ExecuteCommand: Returns an object //
//*************************************************************************************//
Thread t = null;
lock(Locked)
{
SQLString = queryStr;
switch(type)
{
case "ExecuteNonQuery":
t = new Thread(ExecuteNonQuery);
break;
case "ExecuteScalar":
t = new Thread(ExecuteScalar);
break;
case "GetDataReader":
t = new Thread(GetDataReader);
break;
}
t.Start();
t.Join();
}
return null;
}
First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated.
I have 2 tables, Customers and Organizations. 1 Customer can be under many organizations. What would be the best way to design the db (2 choices) for performance (around 50000 customers): 1) -Customer table -Organization table -Link Organization & Customer by creating a new tables with the following structure ---> CustomerID, OrgID,
2) -A Customer tables that has a field called OrgID where the orgID is stored. If the customer has more organization related to him, we add another customer record and we put the new OrgID in the OrgID column. Examples:
Customer Table -------------- CustomerID--Fname--Lname--Addresss-----OrgID --------------------------------------------- 1 Bob Marley 33 Africa org1 1 Bob Marley 33 Africa org2 1 Bob Marley 33 Africa org3
I need some other opinions on whether or not this is considered a proper database design structure.
Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.
PERSON_TABLE Person_ID Company_ID
COMPANY_TABLE Company_ID
Then each person can trust other people of other companies, but can only trust 1 person per company.
My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.
I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:
BuildingConstructionType relates to ConstructionHasTypes relates to Types
Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).
Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?
Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
I have 2 tables: Table 1 has:ID,FName,Lname Table2 has:ID,PID,PFName,PLName, Flag PID is a unique number -> (hh:mm:ss) I need to run an update to table2 by setting the Flag to 1 and also creating PID. I could use a join query to do this, but I am concern about PID (hh:mm:ss), because it may take less then 1 second to update and I will have duplicate PID. Any idea what is the best way of doing this?
Well, its kind of tricky, and i got into this.For example i have a column of zip, all US city state name and abbreviation, preffered name and an alternate name of each city in US, and also non-acceptable name of each preffered name..
Can you give me a hint, how many databse should i used? for example, i search for a zip code, and the result will be the name of city or an acceptable name of city and there are non-acceptable name too..
well.i just need your opinion..hope you could get into this one.tnx
This is more a theoretical question so I do not have any DDL (working)to post.Let's say that I have a query which needs to be filtered for specificaccounts while also needing several joins to retrieve additional data.Is it better to so one big SELECT / JOIN / WHERE statement? As inSELECT * FROM T1JOIN T2 ON T2.[Col1] = T1.[Col1]JOIN T3 ON T3.[Col2] = T1.[Col2] AND T3.[Col3] = T1.[Col3]and so on...WHERE T1.[Account] IN ('123', '456', '789')OR is it better to do an inner SELECT / WHERE and pass that to a SELECT/ JOIN? As inSELECT * FROM(SELECT * FROM T1WHERE T1.[Account] IN ('123', '456', '789')) ITJOIN T2 ON T2.[Col1] = IT.[Col1]JOIN T3 ON T3.[Col2] = IT.[Col2] AND T3.[Col3] = IT.[Col3]and so on...First glance logic says that the inner select is the way to go sincethe joins would have less rows to work with, as opposed to joineverything and THEN pulling out what is not needed. But the queryplanner sometimes seems to have a mind of its own... Does it know thatrows will be pulled so it does that first? If I follow the same"structure" with many different queries does in us the same logic allthe time or do I need to try the same thing for each and check it?How does this apply to situations where there is a UNION involved? Do Ido the union and then apply WHERE and JOIN to filter out rows and getadditional data, respectively, or do I filter out rows inside the unionand take the combined set and do the JOINS?SELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...WHERE CT.[Account] IN ('123', '456', '789')versusSELECT * FROM(SELECT T1.[Col1], T1.[Col2] FROM T1 WHERE T1.[Account] IN ('123','456', '789')UNION ALLSELECT T2.[Col1], T2.[Col2] FROM T2 WHERE T2.[Account] IN ('123','456', '789')) CTJOIN T2 ON T2.[Col1] = CT.[Col1]JOIN T3 ON T3.[Col2] = CT.[Col2] AND T3.[Col3] = CT.[Col3]and so on...
What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?
Example 1: SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city ORDER BY a.au_lname DESC
Example 2: SELECT * FROM authors AS a, publishers AS p WHERE a.city = p.city ORDER BY a.au_lname DESC
I need to purchase a new computer for a small medical clinic which will basically only have one purpose: to answer to read and write queries to a SQL Server 2005 which is resident on that computer. Queries come from the current 8 stations (up to 14 stations in the future). Most of the time, only 3 stations will be active at a time. Queries are mostly to access patient file information, are not complex and are short-lived.
A friend of mine who owns a computer store just quoted me for a dual quad-core Xeon 5405 2GHz system with Windows Server 2003 10 Cals. I'm concerned about the following: - What's the use in having 8 cores, each of them running at only 2GHz, when there's really only one service running (SQL Server 2005, likely Express Edition) on the computer. Does SQL Server have the capability to make use of all cores? Otherwise, why spend more for Xeon and so many cores instead of a single C2D running at a faster speed of say 3GHz ? - What would be the advantage of using a Windows Server over Windows XP in a peer-to-peer configuration? I don't buy into the 10 connection limit because the TCPIP.sys file can be altered to move that limit up, so 14 stations does not trigger the need for Windows Server in and of itself.
I was talking to my boss to day and our report request are not very consistant. We always having someone coming back to change something in our report. We were thinking of useing something called the Cube Analysis. Then it give our employees the raw data for them to run any standard query for themself. We have folks that want a report one way, but then they changed their minds and we are creating yet another report 4 or 5 times. what are your thoughts about this type of database?
What's the current opinion on UDTs? Are they valuable? Do the benefits outweigh the costs? Are they an absolute no-no? Has there been anything authorative or groundbreaking on the topic since Alex P's blog back in October 2005?
I am not that experienced in SQL 2005 and I'm trying to debug a stored proc (written by a far more experienced programmer which is why I'm appending here.)
At the top of the proc is the statement 'DECLARE @BillActualRoom bit'. If I understand correctly this is a local variable.
Later in the code we have the statement
'SELECT @BillActualRoom = 0 FROM BillingOptions WHERE CenterID = @CenterID'
where @CenterID is an input parameter.
The table BillingOptions does have a field called 'BillActualRoom' and it is a bit.
My question is this: Does this SQL statement make any sense at all? (My gut reaction is no but I'd like a second opinion. Can't get any consensus in the office.)
I would think that putting a local variable in like this would mean that @BillActualRoom is always equal to 0.
Trying to deal with a user complaint of slowness. Many variables looked at which look normal (Buffer cache, queue length,memory). Probably looks like a network issue. My question is what people consider acceptable when it comes to %disk time. My %disk time has increased from an average of 20% to 33% in recent months. My average disk read and average disk write have both been less than one. MY research has showed that more than 55% %disk time for ten minutes is considered a problem. Not there yet but seem to be slowly getting there. THe app running against my server is vendor written so can't change, also running log shipping which is probably inflating the numbers a little. Any opinions appreciated.
I have a server with little control over most of the codeset and db design. Recently I have seen both the Processor - %Processor time and Processor - % User time go fom about 6.3 to about 24.3. The system queue length has also gone from about .2 to 1.1. In my humble opinion both of the are signs of a problem coming (luckily the cache hit ratio is still sitting at about 99%). I have been running profiler to catch the things that take more that 4500 MS, and I can probably tie the 2 together. Any opinions, or real world comparisons appreciated
We are trying to restrict developer permissions in our development environment. One thought is to add developers to db_datareader, db_datawriter, db_ddladmin, db_securityadmin and then revoke various permissions from ddladmin and securityadmin. The goal is to allow developer to create stored procedures and assign permissions to the stored procedures.
Another option is to place all developers in the same role and ask them to create all procedures using that role name (ex: dev_role.sp_procedurename). By doing this each developer will be able to run stored procedures created by another developer. The down side is the permissions do not match Model Office/User Test and Production.
I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:-- Here the table that is referenced first was "declared" first. SELECT* FROM Logon LEFT OUTER JOIN Thread ON Logon.LogonID = Thread.LogonID LEFT OUTER JOIN Message ON Thread.ThreadID = Message.ThreadID
-- Here the table that is referenced first is the table being joined directly above it. SELECT* FROM Logon LEFT OUTER JOIN Thread ON Thread.LogonID = Logon.LogonID LEFT OUTER JOIN Message ON Message.ThreadID = Thread.ThreadID I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other.
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
I inherited this website from a previous developer that uses an Access DB as a backend. Ugh. Apparently, he was extremely limited in what databases he could use. The website is filled with problems related to connecting to this database, which is no surprise. Most of them are related to permissions to the database file, multiple user access, etc. I am thinking about moving this to a SQL Server Express DB and upgrading the site to the 2.0 framework. Most of my experience is in SQL Server, but since they can't, or won't, pay for the SQL Server Database, is this a good solution? I've considered using MySQL as well, but their host won't provide support for this. Are there any other recommended alternatives?
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005 I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
The sqlserv.exe takes too much CPU utilization on my SQL 2000 on W2K production machine. I am tring to use System Monitor to monitor the Thread/%process time with all Sqlservr instances, and then match the sqlservr instance number to the KPID in sysprocesses table to find out which user is causing the problem. but I can only see the instance number from Sqlservr0 to sqlservr99. From the table sysprocesses table, the KPID is all 3 or 4 digits number. Any one has any idea about this?
ProductCode - CenterId - Region 13265 - 10 - Asia 13265 - 12 - Asia 13265 - 9 - America 11110 - 10 - Asia 11110 - 9 - America 12365 - 12 - Asia 12365 - 8 - Europe 45620 - 10 - Asia 45620 - 12 - Asia
What I need this query to do is to pull one instance of a product code where the "Asia" appears more than once within the table? Thanks for the help!
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
The thing that I dont understand is, where the thread get the information from? My windows application currently set to "zh-CHS" culture but my pc that running the application is "en-US".
It seems like the thread get the information from my pc. What i want is, getLanguage get the information from the application.
I receive the following errors while browsing the http://server/reports page.Please help me to resolve the issue.
Error1.
Thread was being aborted. 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.Threading.ThreadAbortException: Thread was being aborted. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [ThreadAbortException: Thread was being aborted.] System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +483 System.Web.HttpApplication.ResumeSteps(Exception error) +539 System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData) +144 System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +300
Error2.
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help Bad Data. (Exception from HRESULT: 0x80090005)
I have identified that one of the instance is taking much more CPU than the other by using the counter process\% processor time in perfmon. So we have decided to monitor only that instance using ID Thread counter to see which process is causing this high usage within this instance.
Now problem... Thread ID is re-used by Windows. We cannot identify which counter is the one causing problem at that point of time just using the ID from a report given to me NEXT DAY because I can never dream of monitoring the server in the real time to see what is the process Thread ID NOW.
Now question.. Is there is any counter that says which process caused the spike in %CPU used at one point of time from the thread id comparing against another counter in perfmon?
I have never worked with this in detail before. If any one can give me the counters and the method to identify which process is related to which thread at that point of time from a report received NEXT DAY MORNING, thanks a lot.
Hello, I have built a system, that uses a .dll file for all SQL operations. So a program looks something like this: using myDLL; .... SQLDBCON mSQL = new SQLDBCON(); and here comes the rest of the program. My question is, the dll file has all stored procedures and when you declare mSQL as shown above, then it opens a thread to the database for that user. Is that thread properly closed when the site has finished loading? in my .aspx page i dont have a function like mSQL.CloseDB(); and if i try to add something like this to the dll file ~SQLDBCON { m_local_con.Close();m_local_con.dispose(); } i get a error message says something that this is not allowed. Just want to know if my thread in the dll file is properly closed?
I am currently using @@Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @@Identity from table",conn);string identity=cmd.ExecuteScalar();Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I'm making a "simple" ASP forum to integrate into a webpage solution. I just recently ran into a problem with sorting threads. I have a page that displays all threads in a given forum (Threads.ThreadForumID=iForumID) this page will have all pinned threads on top of the list the threads should then be sorted according to when a message last was posted inside the thread (Posts.PostDate)
Sort order: Threads.ThreadPinned Posts.PostDate or Posts.LastPostDate if it has been set.
The tables are as follows: Threads table: ThreadIDNumber ThreadNameText ThreadViewCountNumber ThreadOwnerIDNumber(Ref to thread owner id, user) ThreadLockedBoolean ThreadPinnedBoolean ThreadForumIDNumber(Ref to ForumID, forum)
Posts table: PostIDNumber PostTextText PostOwnerIDNumber(Ref to post owner, user) PostLastEditByNumber(Ref to last user that edited, user) PostThreadIDNumber(Ref to thread) PostIPText PostNicknameText PostDateDate PostLastEditDateDate
Current "sort": This is the current SQL statement it is not in any way correct. It's just temporary. I have tried different joins but I can't seem to get it right!
SELECT ThreadID FROM Threads WHERE ThreadForumID=" & iForumID & " ORDER BY ThreadPinned DESC, ThreadID DESC
I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved) I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem
my question is: can I change this behaviour somewhere?
The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:
first a select is performed to fetch a certain record in the file that gets locked next an update is performed with following syntax:
update ... set.... where current of 'pointername'
this update is waiting for a lock to be released before the batch continues.
My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed. The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people :)
If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful