I think my use case is not good for relational databases
Why you are thinking a relational db is wrong here? For me it looks like a rdb fits very good.
before adding a new entry, check if the key (string) is already stored
For this you can define the key as Primary or at least Unique. So, before insert you do not Need to check whether it is allready in. Simply insert, the db will tell you then, whether it was allready in. The Advantages: No multiply checks for unique values(one from you explicit, one from DB while checking constraints) and last but not least, no race condition.
One Thing more, MS SQL with its "Clusterd Index" fits Performance whise perfect for key/value pair.
There is only one Thing: "billions of string/number values" looks like you can not go for the free Version because of restricted db size.
Thanks for your answer.
I just thought that a key/values store meight be a better solution. They are usually designed to be fast with key/values... But I'm not very experienced with NoSQL DBs, so I meight be wrong with this.
If I use a relational db I know how to use PK. For the first table I still need to read it first, because if the key already exists, I also need to update the value of this entry. For the second table it would work just to let the DB check the constraint if the entry already exists or not.
MS SQL is not my first choice, because I also want it to work on Linux systems as well.
Some points to consider:
- are your keys case-sensitive? In MS SQL, string comparison is normally not case-sensitive, while with postgres or Oracle it is.
- what is the relation ship between the two tables? Do I understand you correctly that there are keys which exist in Table1 only, and other keys which exist in Table2 only?
- do you need some kind of reporting? I.e. how many different keys can be found for a value for keys exiting in both or only one table. Complex aggregation queries work fastest in MS SQL or Oracle, while mysql copes with simple aggregations only (but with two tables, that should still be ok).
- What would you do if a key to be added already exists? Update the record or throw an exception?
Thanks for your reply.
- the keys are all in uppercase in both tables, so a case sensitive match works perfect.
- The first table is the leading data store, the second table is more or less a lookup table. I want to check which entries in the lookup table exists also in the data store table.
- I don't need to search for any values, I need to search only for keys.
- If a key in the first table already exists, I need to update the value (increase the value)
- If a key in the second table already exists, nothing happens, just continue with the next one. But inserting doublicated entries in the secend table are extremly unlikely, so there is no need to check them. It's no problem if for some reason there are really doublicates, to add them twice. But if I use a rdbms and define the key as PK, this check is done automatically.
I will give it a try with a rdbms (not MS SQL because I want it to be available also on Linux systems).
But I'm wondering if a simple NoSQL DB (simple key/value) store meight not be faster in this case. I don't have much experience with NoSQL dbs, but I think they exist for a reason. And I want a really lightning fast solution
But if you think a normal sql db can be as fast (or faster) than I give it a try.
Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components isset on database connection manager.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
1. DelayValidation is set true.
2. Excel Connection String is set as
Error 3 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. ---> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0
but, when i put HDR=YES the error was change. it says neesd new metadata, its seem like i have different format of file. event thought the file was the same.
It is very difficult to guess what is going on without more information about the actual code that causes the exception. Please show the code where the error occurs, indicating the exact line that raises the exception, and the exact content of all variables that are being used.
Hi every body,
I have a very special an uncommon! issue with CDC data migration, this is my problem: we had enabled CDC backup on our DB setting cleanup date for 2 years, so after 2 years we have old CDC data only in DB backups, now our customer needs reports base on CDC data in past 4 years, so the only way we can do that is reinserting old CDC data in corresponding tables by script, we replaced "change_tables" and "lsn_time_mapping" and set the data cleanup date for 10 years. every thing is OK until the cleanup job runs and removes the old data, is some data missed for restoring or is any setting avoid this cleanup?
every suggestion would be appreciated
SET source_table.title = (SELECT destination_table.title FROM customers destination_table WHERE destination_table.customer_id = 18)
FROM customers source_table
WHERE source_table.customer_id = 14
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM customers source_table
WHERE source_table.customer_id = 14' at line 21
"Conversion failed when converting the varchar value 'AS' to data type int."
The problem is that I have no field or variable named "AS". So I am really stumped. The complete code of the stored procedure is below: (I have marked the location of the error with a comment.)
Do you have any idea what it's talking about? Thank you very much for any pointers in the right direction!
-- Add the parameters for the stored procedure here@VENDORIDINT,
@DELETEDBYIDINT, -- Pass 0 if not deleting the record@DELETEDONDATETIME,
ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;
-- Insert statements for procedure hereBEGIN TRY
IFEXISTS(SELECT ID FROM VENDOR_MASTER WHERE ID = @VENDORID)
BEGIN--<< SQL SERVER SAYS THE ERROR IS ON THIS LINEIF (@DELETEDBYID > 0) AND (NOTEXISTS(SELECT USERID FROM USERS WHERE USERID = @DELETEDBYID))
BEGINRETURN2; -- DeletedById does not exist in USERS tableENDIF (NOTEXISTS(SELECT * FROM COUNTRY_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))
BEGINRETURN3; -- Country Code does not exist in COUNTRY_CODES tableENDIF (NOTEXISTS(SELECT SHIPVIACODE FROM ORDER_HANDLE_MASTER_SHIPVIA_CODES WHERE SHIPVIACODE = @VIACODE))
BEGINRETURN4; -- VIA CODE does not exist.ENDUPDATE VENDOR_MASTER SET ACTIVE = @ACTIVE,
COUNTRYID = @COUNTRYID,
VENDORCODE = @VENDORCODE,
NAME = @NAME,
WEBSITE1 = @WEBSITE1,
WEBSITE2 = @WEBSITE2,
VIACODE = @VIACODE,
TERMS = @TERMS,
USECOMPCODE = @USECOMPCODE,
DISCOUNTPERCENT = @DISCOUNTPERCENT,
DELETEDBYID = @DELETEDBYID,
DELETEDON = @DELETEDON,
NONUS = @NONUS,
NOTES = @NOTESWHERE ID = @VENDORID;
ENDELSE-- IF EXISTS VENDORIDBEGINRETURN1; -- VendorId does not exist in Vendor Master tableENDEND TRY
SELECT ERROR_NUMBER() [ErrorNumber],
The difficult we do right away...
...the impossible takes slightly longer.
modified 8-Jul-16 16:46pm.
Last Visit: 31-Dec-99 19:00 Last Update: 29-Jan-23 9:29