I notice that SQL Server 2005 creates worktables where SQL 2000 does not. Often these work tables appear in STATISTICS IO, but they show a 0 scan count and 0 logical reads. These worktables often appear to be substituted for bookmark lookups.
Has the optimizer decided to use worktables instead of bookmark lookups (often resulting in a higher cost plan)?
Sharon
I am just getting started studying SSIS with Kirk Haselden's "Integration Services" book. The problem I am trying to solve would seem easy enough to solve in code, but I am still early in the book and would like to be able to focus on the aspects of SSIS that would help me expedite this with SSIS, or to find out early whether what I need to do cannot be easily done.
The problem itself is simple enough: I have a database of roughly 100 tables. Ignoring the poor normalization in the database for the moment, my more pressing problem is that that I need to rekey all of the main OLTP tables from a mashup of different key schemes to UNIQUEIDENTIFIERS. For example, Client table is presenrly keyed on an INT, Client Number. ClientFile table is keyed as FileType = NVARCHAR(2), ClientNumber INT, FileNumber INT (incrementing, meaningless number). Child tables to ClientFile are the same key structure as ClientFile, plus yet another (incrementing, meaningless number) INT, etc like this. I would like to know if and how or where I should be looking to convert the Client table to a UNIQUEIDENTIFIER key, and the same for the ClientFile, makes its key also a GUID, and have a reference to the new Client tables GUID key as a foreign key in the ClientFile table, and on and on like that. The Client is at the top of the food chain.
In essence, I would like to have every table's key be called ID and be a UNIQUEIDENTIFIER (GuidRow), and I would like for example, the ClientFile table to reference the Client table with a column named ClientID. I would like ClientFile's children to have a foreign key called ClientFileID, and their own keys to be ID (RowGuid).
There are also several lookups in each table where, of course, the actual string values were stored instead of a key to the value (i.e. full state or country name instead of a code from a state or country lookup table) that I need to convert to something more sensible, like replacing the state name with a state code and a country name with a country code and link to appropriate respective tables. :) In fact, some of the values I need to break out from columns could also be keyed with RowGuids as well and I would much prefer to use those than string or INT keys.
Other than those problems, most of the rest of the data in those tables could essentially ba a straight copy operation since the source database is SQL Server 2000 and moving to SQL 2005 (one notable exception is that I am converting ntext columns to nvarchar(MAX) columns.
I am assuming this is probably ridiculously simple and I just haven't found my way there yet, but I still have much of this book and the help files to go through and the Index didn't give me any comfort that this was something I will or will not be able to do easily.
The real help I am looking for is two fold: a) somebody tell me to stop reading this 700 page (very well written) tome if I would be better off writing this all in code myself, and b) if this is something that most of you could do with SSIS with both hands tied behind your back, please at least help me focus on the important transforms and tools so that I don't smend a month becoming a data warehouse wizard and ultimately not solve the problem I am most concerned with.
Please be mecriful with the heat, I have already confessed that I am new to this and am scrambling to come up to speed as fast as I can, but am beginning to think this problem is either to trivial for coverage in this book, or perhaps just not what SSIS was designed to do.
Thanks much in advance for any guidance.
execution plan of procedure shows
bookmark lookup cost : 46%
any way to reduce it ?
Thanks
alex
okay, so i've been slowly working on an application mostly VB/ASP.Net based that is basically i dummied down query builder. the user selects a bunch of controls off the website, then submits it for a query. the server then executes a dynamic stored procedure, and the website builds a count with the results. i have a couple of questions, i've come to the point where if the query isnt finished inside 30 seconds, the site times out, so i'm looking at a query here, and i have a "BookMark LOOKUP" that is costing me 32% of the query. what can i do to stop that?
how can i get my indexes to perform faster, and is it an issue with the procedure that causes some queries to time out, or is it just the way the data is organized?
Hi,
Please tell me how to implement the "Jump to Bookmark" in reports,
I mean to say, i have to move to another area or page in a report.
Please provide a sample for the same.
Thanks and Regards
Altaf Nizamuddin
I have a report in reporting services that has a bookmark value asigned to each row. In the following Page_Load event for the page, I'm getting the following error on the ReportViewer1.JumpToBookmark("6") statement:
"An exception of type 'System.InvalidOperationException' occurred in Microsoft.ReportViewer.WebForms.dll but was not handled in user code
Additional information: Some parameters or credentials have not been specified"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim strReport1 As String = "/ReportDirectory/CurrentWeekSales"
ReportViewer1.ServerReport.ReportServerUrl = New System.Uri(CStr(Session.Item("ReportServer")))
ReportViewer1.ServerReport.ReportPath = strReport1
ReportViewer1.ServerReport.Timeout = 500000
Do While ReportViewer1.ServerReport.IsDrillthroughReport
ReportViewer1.PerformBack()
Loop
Dim RptParameters1 As ReportParameter() = New ReportParameter(0) {}
RptParameters1(0) = New ReportParameter("EBO_EmpKey", CStr(Session.Item("UserKey")))
ReportViewer1.ServerReport.SetParameters(RptParameters1)
ReportViewer1.ServerReport.Refresh()
ReportViewer1.JumpToBookmark("6")
End If
End Sub
What am I missing?
Thanks
When a nonunique nonclustered index is built on top of a clusteredindex, is it guaranteed that the bookmark in the nonclustered indexwill be kept in the same order as the clustered index?Here's an example to demonstrate my question:CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3int,col4 int)ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)CREATE INDEX ix_indextest ON indextest (col1,col3)GOINSERT indextest VALUES (1,2,1,1)INSERT indextest VALUES (1,3,2,1)INSERT indextest VALUES (1,4,2,1)INSERT indextest VALUES (2,1,1,1)INSERT indextest VALUES (1,1,1,1)SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1DROP TABLE indextestThe select statement above is covered by the nonclustered index, sothat index is used. However, the nonclustered index is defined only toensure the ordering of col1 and col3 within the index; col1 and col2follow within the index as the bookmark to the clustered index. When Irun this query, my desired result is to have the records appear in theorder supported by the clustered index:1,11,2As it happens, the result I got was indeed in that order, but I don'tknow if it was mere coincidence, or if the bookmark in the nonclusteredindex is maintained in the same order as the clustered index. If Iwant to ensure the above order, is it sufficient to have thenonclustered index defined as above, or do I need to define it as:create index ix_indextest on indextest (col1,col3,col2)just to be sure that the results are returned in ascending order forcol1,col2? If the two-column index is sufficient, is it guaranteed tostill be sufficient in SQL2005 and future versions of SQL Server, or amI better off adding the third column just to be safe?Thank you,--Dennis Culley
View 4 Replies View Related
All,
Is there a way to combine the Bookmark and report link functionalities in reporting services. For example, I have two reports. When I click a link in first report, I would like to be able to navigate to second report but not at the top of the report but at a particular report item way down in the second report, sort of like an anchor on an HTML page. Can I do this?
Thanks.
Is there a way to jump from one report to a specific bookmark in the second report? I have a chart that jumps quite nicely to a filtered version of the report, but instead would like to be able to jump to the entire report at the specific location (e.g., Bookmark) related to the chart data item.
I know that it's quite easy to jump to a bookmark within the same report. What I can't quite determine is how to jump from one report to a specific section in a second report.
Thanks.
I know I can do to just show 1 entry
select * from table where no = @parameter_no
but is it possible to actually select ALL entries (I have 250 for now) in the list report, but jump to a particular # based on parameter?
Say I have ID 1 ~ 250 (select * from table) in DataSet, but when I enter 220 in parameter, the report still selects all 250 entries but jump to the 220 entry?
I have bookmark set up on each entry
Thanks for any help. I noticed in my search that jump to a report + bookmark isn't supported, does my situation fall under that?
I am using 2 charts in my SSRS Report. Requirement is to load the Right side chart based on the click event from left chart.
I can achieve it using go to report option. But the issue is total page getting refreshed while using this option. I feel bookmark option will be the best. But I don't think I can pass a value to Bookmarks.
If you display the execution plan and run the following:SET STATISTICS IO ONgoSELECT ProductID, SupplierIDFROM ProductsWHERE SupplierID = 1I don't understand how come there is noBookmark Lookup operation happening to get theProductID?I only see an Index Seek happening on SupplierID.There is no composite index SupplierID + ProductIDso what am I not understanding here?Thank you
View 3 Replies View RelatedI want to bookMark from one table to another in current report. Below are the 2 tables , when I select a
FromBrandName in the first table, it should take me to the second table row with the same
FromBrandName. Is this possible? Is there a function to access a specific running value other than First and Last?
dont open the "How To Delete All Tables" post
it's view count is 666
I guess the devil really is in the details
Hello everybody,
have following problem:
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt....
the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
Consider following code:
SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
InfoPath 2003
I have an Infopath form to edit the values in the NotifyMembers table. I want to list the email addresses alphabetically when the query returns data to the form. Unfortunately, I use EmailAddressID as an int in the table, so I need to join? the NotifyMember table with the EMailAddress table, which has a column EMail which is the address. Both have EmailAddressID as common fields.
I can't get the Shape command (edit SQL in the Datasource) to be happy with any syntax that I have tried.
For (non-working) example:
select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as "NotifyMembers" Join "dbo"."NotifyMembers"."EmailAddressID" On "dbo"."EmailAddress"."EmailAddressID" = "dbo"."NotifyMembers"."EmailAddressID" order by "dbo"."EmailAddress"."Email"
Any help appreciated..
I saw a DTS Lookup sample here on SWYNK, but I still have no idea what they're talking about.
Can someone dissect this piece of code and tell me what it does?
DTSLookups("TransformSpecialty").Execute(DTSSource ("LocalSpecialty").Value)
You can reference those tables they have in the example. http://www.swynk.com/friends/green/dtslookups.asp
Thanks!
Lost, ttlai
Hi,
My process passing 1,000,000 rows to a data flow with about 20 lookups to get the keys that I wantted .
Most lookups have small number of rows except one with over 5,000,000 rows. I cannot get the process to run (the process hanged) probably because of memory issue. Any clue where/how I can tune it.
Thanks
I want to do a lookup on date column. My lookup date is of type smalldatetime, and my date is of type datetime (date with time component). My lookup is failing because of incompatible data types.
How do I perform the lookup with date columns having date and time components?
Im having a little diff speeding up the below process
I have two tables table a contains 300,000 rows which are unique however the identifier can appear more than once. The fields im interested in are the identifier and a date/time field.
Table two also contains an identifier and a date field and again can contain multiple instances of the identifier with a variyity of dates.
For each row in table a i want to review the date and look up table b for the first date greater then or equal to the date linking by the identifier.
i have managed to do this via the code below however it takes 45 mins and i want to speed this up.
Select
a.*,
(select Min(DateB) as DateB From #tableB b where a.identifier = b.identifier and b.DateB >= a.DateA) asDATE
From #TableA a
Hello,
I have a source table with few million rows in it. As a part of transformation, I need around 10 lookups in 10+ different tables, all of them having few million rows each. I am looking for an approach that would be reasonably speedy and easy to manage future changes.
Here are some of the things that I have tried...
(1) If I implement as lookup components, they cause developer machine to go really slow and takes forever to run.
(2) I tried having OLE DB Source query to fetch required data up front. But the source query becomes very complicated which will be even harder for future changes. And this big query cause SQL Server to become unresponsive.
(3) Update queries on target table are also causing server to be unresponsive.
What would you guys suggest for this type of implementation?
Kapil
I have some SQL experience, but nothing past basic commands. I'm trying to take some data held by an application to use as CSV import into another application.I have two tables from an application, one holds references made in another.The first tables holds details about a person:
field1=name field2=age field3=country
Joe,50,1
Country is held as a number, then there is another table that holds all the countries:
field1=id field2=description
1,USA
2,France
3,Germany
I want to do a lookup where it returns:
Joe,50,USA
Hi,
We use lookups to join a few huge tables in SSIS (each has more than 40 million rows). The process took almost two days to complete when we select partial load on lookups. It stops/locks if we select full load on lookups.
We have a 32bit server so SSIS uses only 2-3GB of available memory no matter how big RAM we have. It seems the best solution for my problem is to move to 64bit server so SSIS uses up to 16GB of Ram.
For now I am researching for a remedy solution to get better performance from our current environment while we are waiting for the big server.
I’d like to hear your thoughts and options that may improve the performance of our package. Dose partitioning help? What else could be helpful?
I have a dimension table for Retail Order Size. Each row in the dimension has a Starting Value and Ending Value column pair. In TSQL, the correct RetailOrderSize key is found by using the BETWEEN statement, like so:
SELECT RetailLevelKEY
FROM dbo.DimRetailOrderSize
WHERE @Sec1Retail BETWEEN StartingValue AND EndingValue
Is there a Data Flow Task Transformation in SSIS that replicates this functionality, or some other way of getting to the same answer in SSIS?
Thanks in advance for your help
Whenever we open a task and choose the lookups tab ellypsis button we get this error
Microsoft SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.
If you were in the middle of something, the information you were working on might be lost.
Any idea what might be causing this? Am I missing something?
When do a fact table load...I have to perform lookups against the dimension
tables. The dimensions tables I have support slow changes, however, and thus
have multiple rows for a single legacy key under different effective start
and end dates. In order to do this lookup, I have to not just join on the
legacy key, but also validate that the date of the transaction I'm loading is
between the effective date range of the dimension item.
It seems the Lookup task only supports equijoins. Am I missing something
here? How is this accomplished if you can use greater than or equal to and
less than type join conditions?
Ive got an ETL process I have written which takes about 10 million rows from a staging database and loads it into production database with an INSERT statement. The INSERT statement makes a function call to retrieve the surrogate key for each row. The function looks in a replicated copy of our production database so no load is on our production environment during this time.
So: INSERT INTO foo(...) SELECT name, address, zip, dbo.fnGetSurrKey( name, address)
It took about 12hrs to insert 6 million rows last night and Im wondering if there is a better way of doing this. Maybe a multithreaded way like SSIS might have.
Assuming my function is optimized as much as possible, does anyone have any tips for speeding this up?
Also, the machine this ran on has 16gb of RAM but was setup to use only 2GB during this process. I have already changed it to 12gb and restarted the process a week ago, but the change doesnt take affect until you reboot. Would I see a significant performance increase from that?
Is there an easy way to get the Lookup component to ignore case?
View 1 Replies View RelatedSo I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.
I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.
Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.
What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.
Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.
In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)
Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.
Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?
I am trying to make a SSIS package..
In the dataflow of my package, I must check from one table whether a row exists, and if that row exists, I should get some other row from another table, and update that..
I think to check whether a row exists, i should use "Look Up"
But cant we pass parameters to LookUP?
I am trying to use this SQL:
SELECT count(*) FROM ServicePackets where ID = ? and CHANGEDATE > ? and status = 1
I should get if that row exists or not only... (true or false)
How can i use parameters in LookUps?
OK I have this table I am grbbing from Oracle and I need to take selected columns and do a value lookup against another table: IE Here is a list of fields I get from Oracle:
Code SnippetENTRY_ID
SUBMITTER
CREATE_DATE
ASSIGNEE
LAST_MODIFIED_BY
MODIFIED_DATE
STATUS
SHORT_DESCRIPTION
CATEGORY
TYPE
ITEM
SCHEMA_NAME
SCHEMA_KEYWORD
ID
FULL_NAME
USERNAME
PAGER
PAGERPIN
PAGING_METHOD
RECORD_TYPE
GROUP_ID
GROUP_ALIAS
APPLICATION
NOTIFICATION_SERVICE
ARS_GROUP_NAME
GROUP_TYPE
AUDIT_TRAIL
FULL_NAME_
RESPONSIBILITY
PAGER_EMAIL_ADDRESS
Now I need to do a value lookup on these fields:
Code SnippetSTATUS
RESPONSIBILITY
In this example it is only 2 but in other conversions it could be 20 or more... Now here is my select statement for each Field (The ? being the FIELD from before IE Status or Responsibility):
The is for Status:
Code Snippet
SELECT VALUE AS STATUS_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Status')
This is for Responsibility:
Code SnippetSELECT VALUE AS RESPONSIBILITY_VALUE
FROM Field_Values
WHERE (NAME = 'Project Name') AND (ENUMID = ?) AND (FIELDNAME = 'Responsibility')
So for this the way I am doing it now is I have 2 "Lookup" Components setup... It works fine... However as I said when I get say 20 or so it gets really tiresome. I was wondering if I could feed it a EXCEL or XML file saying these are the fields that need a value lookup where:
FIELD - Project - FIELDNAME VALUE - OUTPUT VALUE
So with this example I would have a file saying something like this:
STATUS - Project Name - Status - STATUS_VALUE
RESPONSIBILITY - Project Name - Responsibility - RESPONSIBILITY_VALUE
Then it runs whatever and returns the *_VALUE for each row it goes through... Any suggestions?