|
Well you've not given us much to go on...
You might as well just google <your database=""> optimisation tips...
|
|
|
|
|
hello all
i am working on one of my assignment about sql win app. with stored procedure.and there is two different tables with ralation to PlotID so it's working fine. now only problem is this application must have functionality to add multiple customer names with related land plot.
At the moment i can add one name and one land plot
i mean if i add some details about land plot and name of customer then it works fine but now i need to create functionality to add multiple customer name with same land plot bueause one land plot may have multiple owners.
this is my insert sp
so one of my friend said you can use multiple parameters to add multiple names but what if i need to add 3 or 4 names
so this is what i have done according to my friend and this is also working fine.
ALTER PROCEDURE dbo.InsertNew
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@FirstName2 nvarchar(25),
@LastName2 nvarchar(25),
@Village nvarchar(25),
@District nvarchar(25)
)
AS
SET NOCOUNT ON;
DECLARE @PlotID int
INSERT INTO PlotDetails (Village,District)
VALUES (@Village, @District)
SELECT @PlotID=SCOPE_IDENTITY()
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
UNION ALL
(SELECT @FirstName2, @LastName2, @PlotID)
RETURN
and this is vb for insert record
Private Sub InsertNew()
Try
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBox1.Text
Comm.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBox2.Text
Comm.Parameters.Add("@FirstName2", SqlDbType.NVarChar).Value = FirstName2.Text
Comm.Parameters.Add("@LastName2", SqlDbType.NVarChar).Value = LastName2.Text
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
so is there any other way to do this i mean loop in sql?
waiting for your kind help
thanks
|
|
|
|
|
Try this
Break your InsertNew into two SPs. One for PlotDetails with @Village and @District parameter and save into PlotDetails table. Create a second SP to save CustomerDetails as
ALTER PROCEDURE dbo.InsertNewCustomerDetails
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@PlotID int
)
AS
SET NOCOUNT ON;
IF @PlotID IS NULL BEGIN
SELECT @PlotID=SCOPE_IDENTITY()
END
INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])
(SELECT @FirstName, @LastName, @PlotID)
SELECT @PlotID AS PLOTID
Now in your vb code send Null as plotid for the first time and use the same plotid generated in first time next.
|
|
|
|
|
hello
thanks for your rep.
ok but is this going to loop in sql quary?
for adding multiple names this is what i have done
Private Sub GetNewName()
AddName.Add(TextBox1.Text)
AddName.ToArray()
End Sub
so with this i can add one name or 100 names and i have debug this code and it's looks fine but only thing is how can i insert all this name from array to database
so in that case i have to loop while i am inserting
so this is i have done for insert
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
For I As Integer = 0 To AddName.Count
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Next
Comm.ExecuteNonQuery()
Conn.Close()
MsgBox("Item Saved")
and it looping through all the name i have entered but at last it shows error
error "Index was out of range. must be non-negative and less then the size of the collection parameter name index"
waiting for your kind help
|
|
|
|
|
Not this way.
First Execute
Comm = New SqlClient.SqlCommand("InsertNewPlot", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString
to Insert Plot where InsertNewPlot is the first SP to insert plot
then something like this
Dim iPlotID as integer
For I As Integer = 0 To AddName.Count
Comm = New SqlClient.SqlCommand("InsertNewCustomerDetails", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Comm.Parameters.Add("@PlotId", SqlDbType.NVarChar).Value = iPlotID
Dim da As SqlDataAdapter = New SqlDataAdapter(Comm)
da.Fill(ds)
iPlotID = ds.Tables(0).Rows(0)("PLOTID")
Next
|
|
|
|
|
I am just sitting here running some manual queries against some DB tables and thought that it would be GREAT if we could get a token that somehow meant the reverse of what * means in a select query.
SELECT * FROM [TableName]
Gets us ALL the columns, so if we want to grab a majority, but just skim off a few we need to enter in all of them leaving off the few we don't want.
Why not allow us to do something like this?
SELECT * EXCLUDE FieldName1, ... FROM [TableName]
This seems much cleaner to me. Oh yeah, and it is less typing.
I know, I should be using a tool that allows me to do my queries in a GUI and then it would just be drag and drop to get the columns I want, but we don't always have that luxury. It seems to me that the intent would be much more explicit to the DB engine and thus provide better optimization also.
|
|
|
|
|
You know, you ain't getting that 
Generally speaking it is better to list all your columns instead of using SELECT *. It does not have to do unneccessery lookup and your contract is fixed. You know what you will be getting. So, one disadvantage of
SELECT * EXCLUDE col1, col2 FROM tableName
is that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.
|
|
|
|
|
|
Ray Cassick wrote: popped into my head
Go wash out you mind, preferably with beer is is starting to stray into fantasy land again.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Why not write a query that returns all fieldnames ex the ones you specify, and copy/paste the result into your script? 
(Second hint, you could automate that task if it's important enough to spend time on)
I are Troll 
|
|
|
|
|
Sheesh, sounds like work
yeah, yeah... I know... Just ramblings of a tired mind on a Friday afternoon.
|
|
|
|
|
Use Red-Gate sql prompt, type
ssf [tab] expands to
Select *
From
type the table name, press up arrow and then [tab], all column name are placed in a list, remove the offending column.
With the amount of time I spent in SSMS my fingers have this in memory.
[edit] bloody html markup [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I heave tried to register new server in Sql Server 2008, using SQL Authentication. The registration works but I cannot start it, pause or stop. All the options are disabled. Neither testing for new server registration works.
Waiting for response,
Kujtim
|
|
|
|
|
I have a problem getting the right data from the database.
I have two tables named "Category" and "Items". Under one category, there can be multiple items.
table for category looks like:
catagory_ID | catagory_Name | catagory_Description
and the table for item looks like:
item_ID | cat_ID | item_Name | item_Description
Here category_ID and item_ID are unique keys and cat_ID is the foreign key which is the category_ID.
now i want the query which gives the list of data under typical category in the list format.
e.g if Book is the category Name and book_name1,book_name2,book_name3 are the items under category, it should look like
Book
book_name1
book_name2
book_name3
how to do that? Well i can get something like this:
Book book_name1
Book book_name2
Book book_name3
but I want the category just once in the output..what should be the query for that ??
suchita
|
|
|
|
|
You will need to use a report builder of some sort. Crystal reports comes to mind, but there are others.
|
|
|
|
|
SayamiSuchi wrote: but I want the category just once in the output..what should be the query for that
This type of FORMATTING of the data is not done in the database it needs to be done in the presentation layer, as dj suggested.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi All,
I currently have a problem with two tables in SQL Server 2005. The tables have grown very large and sometimes lock up when small amounts of data are requested from them. The current record count is 9731495(PrintMedia) on the one table and 4126084(PrintArticles) on the other one. Table names: PrintArticles and PrintMedia. Whats worse is that these two tables are joined on each other in sql queries. This is because the database is non-relational. So joins are used to substitute for foreign keys. The performance needs to be improved. The SQL server version is 2005 standard edition - So partitioning is not possible 
Here is a breakdown of the Tables with their fields, types and Max sizes:
PrintArticles:
Analysis int NULL
width float NULL
height float NULL
ArtSize float NULL
ID (PK) int NULL
Cutting text 2147483647 -This field contains on average 3000 characters
CutID int NULL
NewbaseNo varchar 20
AnalysisApplic int NULL
AnalysisUser nvarchar 50
Translations ntext 1073741823
AnalysisApplicDate datetime NULL
AnalysisApplicTime nvarchar 5
PerTranslation text 2147483647
PerTranslated int NULL
This table also has 3 indexes: 1 clustered on the PK and 2 non clustered
PrintMedia
PrintID (PK) int NULL
CuttID int NULL
Graphic int NULL
FpageSection int NULL
Caption varchar 1000
PDFPath varchar 300
Branch varchar 30
UploadTime varchar 10
AnalysisTag varchar 500
UserID varchar 30
Modifier varchar 30
DateModified varchar 10
PubID int NULL
NewBaseArticleNo varchar 20
Edition varchar 800
IS_HardCopy int NULL
Seen_by varchar 300
DTPDate varchar 10
ColourPDF int NULL
SpokesPerson varchar 200
Mention varchar 200
NLPU int NULL
Server varchar 10
Repl text 2147483647
GroupID int NULL
ArticleID int NULL
PubDate 5 varchar 10
CreatedDate varchar 10
Publication varchar 200
SubPublication varchar 200
Headline varchar 800
SubHeadline varchar 800
Journalist varchar 500
SubJournalist varchar 500
Page varchar 20
Client varchar 50
Category varchar 200
CategoryValue varchar 200
CategoryDisplayName varchar 200
OrderID varchar 5
TagID varchar 5
Language varchar 20
Section varchar 200
CCM real NULL
SizeX real NULL
SizeY real NULL
RandValue real NULL
FrontPageCover int NULL
This table has 12 non clustered indexes
I have to restart the sql server service at least once a day to remove locks. If that fails I have to reorganize the indexes or rebuild them to get the tables working again.
The previous developers wrote applications that use adhoc queries. The normal CRUD and the tables worked fine. But scalability was not considered it seems. Will it help if I modify all the select statements in these apps to not have locks eg "select [columns] from [tablename] with (nolock) where..."?. I just need these tables to perform faster and not lock up.
Plz advise.
|
|
|
|
|
Jacobus01 wrote: Will it help if I modify all the select statements in these apps to not have locks eg "select [columns] from [tablename] with (nolock) where..."?.
It feels faster, doesn't it? The documentation on MSDN reveals why it's faster;
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
That means that you could be reading data that's not committed. I'd be very carefull if the database contains a lot of stored procedures and utilizes transactions a lot.
How are the indexes, and what does the profiler say?
I are Troll
|
|
|
|
|
I assume that CutID is the FK to the tables, just wondering why it is not the primary on one of the table? Totally irrelevant to your problem.
I would first put in place a nightly maintenance job that cleaned up the indexes.
As to Eddys issue with uncommitted data, I would assume that queries are filtered and therefore this dodgy data should not be an issue except where you are specifically getting the latest information. There tends to be a small window of data that is subject to change, the last few records in the table. If you give your users a caveat that it may be dodgy I see no reason (nolock) should not be used.
CAVEAT - note I have never used (nolock) so take this cautiously.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If a primary key is dropped from a table and some records are duplicated after dropping primary key, then is it possible to apply the primary key on the same column. If yes, then how??
NEHA GUPTA
|
|
|
|
|
No, not really. The whole point of a primary key is that it is unique for each row (be it a surrogate key or a key made up from several columns). If you have duplicate rows then, by definition, you will be breaking the rule. Remove the duplicate rows and then apply a primary key.
Why would you deliberately remove the primary key and add duplicate rows?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
ya how can again you set the primary key which already have two rows of same key ??? it is not possible...
suchita
|
|
|
|
|
but there is some option when we alter the table, (when we add constraint) with an option "with check" and "with nocheck". What is that option? Please clarify..
NEHA GUPTA
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
