|
|
So I am working with some legacy data trying to clean it up and move it into a data mart.
Scenario
5 files of transactions varying from 10k to 10m rows with 100 columns (yeah I know a disgusting number of columns but I'm still working on it)
I load the csv file into a staging table using BCP with all columns as varchar.
Update the dimension tables
Working with the smallest table 13k rows. Using a select statement with joins to the dimension table to get the FK ids I have 10 inner joins so far.
The Issue
To reduce the response time I added TOP 100 to the select statement and was horrified to get a result in 2:26, when I remove the TOP 10 the execution time DROPS to 16 seconds. I always thought top N wold reduce the response time not increase it by orders f magnitude!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I guess it makes a sort of the set before picking the TOP 100.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
If I had an order by clause I would expect that but there is no order by.
I wonder if SQL 2008 has some implicit ordering (usually the first column in the select statement) that is impacting the query?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It depends on the query plan. But you're having some joins so I would assume so.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I have around 12 joins and adding more as the day dissapears.
I am not bothered about the query plan at this point as I still designing the procedure. I was just astonished that the TOP 10 increases the response time!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you are doing table scans in there, this will have an impact as your code has to read all the rows to determine what the top 100 actually are. The QEP will identify where the problems are.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys | Onyx
|
|
|
|
|
My assumption is that without and order by clause it should not have to scan the table, just get the first 10 records. It was during development so the performance was not really relevant as I had to tune the indexes after stabilising the query. I was just astonished it was slower than the full select.
Transforming this data in is going to take about 6 hours by the look of it, thankfully it is monthly not daily!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey all,
I'm writing a basic parametrized query to search a database and am attempting to allow a user to specify 1 or 2 parameters for a search. However, I am having trouble when the user specifies both parameters as the return of the query then displays 0 rows. Here is what I have:
WHERE ((suburb LIKE '%' + @suburb1 + '%' AND @suburb2 ='') OR (suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'))
@suburb1 and @suburb2 are declared within that statement as parameters for the query (I am using VS2008 to construct it). It looks like I need an IF statement or similar to make it do the first part if @suburb2 = '', however I can not for the life of me work out how to do this...
I am relatively new to writing queries, so I understand that I may be way off with how I'm approaching this, but some guidance in the right direction would be greatly appreciated!
Joe
|
|
|
|
|
Do some research into parameterised queries[^], this will allow you much greater flexibility and control and reduce the risk of sql injection attacks.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Just look at:
suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'
If @suburb2 is different from @suburb1, that will select exactly 0 rows because suburb is either like @suburb1 or like @suburb2 but surely not like both at the same time. Use OR instead of AND here.
To differentiate between @suburb2 being set or not, use a CASE statement.
|
|
|
|
|
|
I hate it when someone univotes a post with no explanation.
You error is that this is a database forum and you are asking a question that requires VBA, I know you think you are using a database - Access but that is because Access should no longer be called a database, abomination maybe but not a database.
You will get a much better response in the VB forum. You should also mention that you are using spit MS Access and the version as there are differences that may be critical.
A possible answer - define some global variables representing the data, load them from the dataset/recordset or make the dataset/recordset a global. Sorry have not used Access for more than a decade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
when i should use nvarchar(max) type and when not?
|
|
|
|
|
I can't say when to use or not, but this link[^] should help with explaining the differences. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
An nvarchar column can stores unicode data but requires twice the space compared to varchar.
A varchar column is restricted to an 8-bit codepage and is therefore limited to one or a few alphabets/languages at a time. Codepage incompatabilities are a PITA, and Unicode is the cure for codepage problems.
Normally you take a performance hit using nvarchar as it takes twice the space in both memory and disc. But if the client uses a different codepage than the server you might take a performance hit on the conversion of the varchar instead.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Use it for strings; not for numerical and temporal data. 
|
|
|
|
|
I don't really understand your question. Are you asking:
- when should I use nvarchar instead of varchar?
- when should I use nvarchar(max) instead of a specified size such as nvarchar(100)?
- when should I use nvarchar(max) instead of ntext?
- when should I use nvarchar(max) instead another datatype?
- something else
- all of the above
These are all different questions (some of which have been answered already).
|
|
|
|
|
Problem we are Facing Currently: Records getting inserted as only one during the process of updating the server.
Previously, all the given records get inserted into the database table during the updation process.
"updateprice" is the .mdb exists locally in the same folder where the calling file for updating records exists. It contains record data to be uploaded to the server database. It acts as an temporary storage database.
The records to be updated to the server are either in an notepad or excel. Whatever be the count in records, previously it was updated fine. "rsTextData.Update" is line 86. I'm blinking where it fails.The error which we're facing and the code is given below for the assistance.
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
code
****
<table width="100%" cellspacing="0" cellpadding="0" align="center">
<tr><td height="25" class="mtext" background="../images/top_bar.jpg" align="center"><font color="#FFFFFF"><b>ADMINISTRATION</b></font></td></tr>
<tr><td height="10"></td></tr>
<tr><td height="20"></td></tr>
<tr><td width="100%" class="mtext">
<p style="margin-left: 10; margin-right: 3">
<center>
<b> Uploading Text Data in progress...<br>
<br>Please wait..</b>
</center>
<%
set myconn=server.createobject("ADODB.connection")
myconn.open "updateprice"
myconn.CommandTimeout=0
set rsData = Server.CreateObject ("ADODB.Recordset")
NewsCodeSelectSql="Select * from text_data"
set rsData=myconn.execute(NewsCodeselectsql)
code = 0
intRecCount = 0
set myconn1=server.createobject("ADODB.connection")
myconn1.open "DSN=commodities;uid=fbspl07;pwd=07#web&;"
myconn1.commandtimeout=0
Response.Write("1 record(s) converted<br>")
do while not rsdata.eof
Set cmdTemp=Server.CreateObject("ADODB.Command")
cmdTemp.CommandType=2
sql="select * from text_Data where 1<>1"
set rsTextData=Server.CreateObject("ADODB.Recordset")
rstextdata.locktype=adLockOptimistic
rsTextData.Open "select * from text_Data where 1<>1",myconn1
msgbox rsTextData.RecordCount
rsTextData.Addnew
response.write "File Name:" &rsdata(3)
'response.write "Title: " &rsdata(4)&"<br>"
rsTextData("Text_id")=rsdata(0)
rsTextData("Date1")=rsdata(1)
rsTextData("Comname_id")=rsdata(2)
rsTextData("Filename")=rsdata(3)
'response.write "Title= " & rsdata(4) &"<br>"
title1=replace(rsdata(4),"("," ")
title1=replace(title1,")"," ")
title1=replace(title1,":","-")
response.write title1
'response.end
rsTextData("Title")=title1
'rsTextData("Title")= rsdata(4)
rsTextData("Source")=rsdata(5)
rsTextData("isspot")=rsdata(6)
rsTextData("Module_type")=rsdata(7)
rsTextData("Submodule_type")=rsdata(8)
rsTextData("Time1")=time()
rsTextData.Update
rsTextData.close
rsData.movenext
code = code + 1
If code mod 25 = 0 then
Response.Write(code &" records converted<br>")
end If
loop
response.WRITE("<br><br><b> Text Data is Inserted Successfully....</b><br><br>")
%>
<p align="RIGHT"><a href="javascript:history.back()" target="_parent"><font face="Arial" size="2">Back</font></a> </td></tr>
<tr><td height="20"></td></tr>
</table>
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Do a SELECT on the table, using the value from rsdata(0) - you'll see that it's already in there.
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Actually, the datas stored in notepad or excel. Then those stored values are copied to "updateprice.mdb". The values from "updateprice.mdb" are transferred to the "text_data" table in server with the method called for updating the values to the server. In this method only i'm facing the problem.
The "Text_Id" field is the unique one and won't have any duplicates in any case. Also, the data team members who are in-charge for inserting datas to server, said that the server updation by inserting is done properly when the record count<=3. If the recordcount exceeds 3, then the error is showing as:
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
I'm blinking and stuck with datas getting updated to the server. Kindly help me.
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: The "Text_Id" field is the unique one and won't have any duplicates in any case.
You'll get the error that you described if you try to insert one that's already there.
swornavidhya_m wrote: If the recordcount exceeds 3
What's the value of the PK-field (Text_Id) in those three records?
I are Troll
|
|
|
|
|
The value of TEXT_ID will be as like 1779562, 1779563, 1779564, and so on.
|
|
|
|
|
There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting?
I are Troll
|
|
|
|
|
I have a stored procedure with two params in sql2005. If I excute the stored procedure it takes 2 min but i copied the same query in new window, declare variables and run the query it takes 1sec. What is the problem.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
